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:
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:
.SetSourceData Source: = Sheets (“examples”). Range (“A1: B8”)
.Location Where: = xlLocationAsObject, Name: = “sheet 1”
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
box left = c.Parent.left
box height = 35
box width = 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.
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.