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:

  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.