Working with charts and reports in Excel can be difficult and time-consuming. If you spend time generating reports from an existing spreadsheet, you might use text boxes to improve the look and feel of a report.
This article introduces the technique of using VBA to customize data reporting by positioning a text box directly underneath a chart and setting an appropriate size.
Creating The Chart With VBA
First, create a simple chart from some existing data. The data table might look something like this:
To create a chart you can use this code:
To position the chart next to the left margin you can set the left property like this:
Creating And Positioning The Text Box
With the chart in place, you're ready to add some text, but first the code needs to know where to position it. We'd like to position the box directly below and in line with the left hand edge of the chart so we need to know some chart dimensions.
We can then code the values to set the dimensions and position.
With the values set, we can now create the text box. The first value, "1" aligns the text horizontally and the final value "60" sets the height which can be adjusted to suit your needs.
Once the full code is run the text box should appear just below the chart and be the same width. Many other editing functions are available using VBA. For example including the following code will write today's date in a short format.
As well as positioning and sizing correctly, you can use VBA to extract information to use as a label or to highlight data.
Summary
While Excel provides the tools to create reports and charts, some basic knowledge of VBA and customization can make your Excel work more efficient and professional.
This article introduces the technique of using VBA to customize data reporting by positioning a text box directly underneath a chart and setting an appropriate size.
Creating The Chart With VBA
First, create a simple chart from some existing data. The data table might look something like this:
Names Sales John 98 Maria 122 Henri 120 Mary 102 Peter 85 Jacques 130 Mary 100
To create a chart you can use this code:
Charts.add
With ActiveChart
.SetSourceData Source:=Sheets("examples").Range("A1:B8")
.Location Where:=xlLocationAsObject, Name:="sheet 1"
End With
To position the chart next to the left margin you can set the left property like this:
activeChart.parent.left=20
Creating And Positioning The Text Box
With the chart in place, you're ready to add some text, but first the code needs to know where to position it. We'd like to position the box directly below and in line with the left hand edge of the chart so we need to know some chart dimensions.
- The distance from the top of the screen
- The height of the chart
- How far it is set from the left of the page
We can then code the values to set the dimensions and position.
boxTop = c.Parent.top + c.Parent.Height + 5
boxLeft = c.Parent.left
boxHeight = 35
boxWidth = c.Parent.Width
With the values set, we can now create the text box. The first value, "1" aligns the text horizontally and the final value "60" sets the height which can be adjusted to suit your needs.
ActiveSheet.Shapes.AddTextbox(1, boxLeft, boxTop, boxWidth, 60).Select
Once the full code is run the text box should appear just below the chart and be the same width. Many other editing functions are available using VBA. For example including the following code will write today's date in a short format.
Selection.Characters.Text = "Report for " & Format(Now(), "dd/mm/yyyy")
As well as positioning and sizing correctly, you can use VBA to extract information to use as a label or to highlight data.
Summary
While Excel provides the tools to create reports and charts, some basic knowledge of VBA and customization can make your Excel work more efficient and professional.
Andy L Gibson is a an Excel developer and writer keen to bring the magic of VBA to new and experienced users of Excel. He has developed an Excel application called "VBA Assistant" containing VBA code and working examples of many of his articles. Enquiries from Excel users interested in downloading the application are welcome.
Andy can be contacted on his blog at http://solutions4business.wordpress.com/By Andy L Gibson
No comments:
Post a Comment