Create And Format An Excel Text Box With VBA

Create And Format An Excel Text Box With VBA

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:


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.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.

Source by Andy L Gibson

Leave a Reply

Your email address will not be published.