How to change textbox name in Excel 2007 and 2010 in a chart tab?

For some reason, after Excel 2003, you just can’t change the textbox name on a chart tab anymore. (It still works in a normal worksheet tab.) And it’s actually a crucial feature since it is how textbox are referenced in VBA. In Excel 2003, you can read the textbox name by clicking on it, and to change it, simply type over its name and press Enter.

And done. In VBA, to want to manipulate the textbox, you will need the name of the textbox to call it up:

ActiveSheet.Shapes("Textbox 102")

It is quite important to be able to see the name of a textbox (or shape) because that’s how shapes are identified in VBA. Somehow, in Excel 2007 and Excel 2010, this no longer works in chart worksheets in Excel 2007 and later!

The Solution

After endless hours of Google searching, I have given up on trying to find a fast way around the problem. I’ve came up with two solutions for this.

1. Cut and paste the box in a worksheet tab and change the name there. Afterwards, cut and paste the box back to the chart tab.
2. Use a macro to solve the problem

Most people will probably just use the first solution, but for VBA geeks (like me) who wants to save the split second of not switching tabs back and forth, I’ve put together this quick hack to solve the problem of finding an alternate way to conveniently:

1. Retrieving the textbox’s name
2. Changing the textbox’s name

To do that, I’ve wrote the following subroutine that fulfills those two objectives on a selected textbox.

Retrieve the textbox’s name:

Sub PrintShapeName()
  Debug.Print (Selection.Name)
End Sub

Note that to see the output of Debug.print:

1. In the Macro Editor window, press on “View”
2. On the drop down menu, click on “Immediate Window”

Changing the textbox’s name:

Sub SetShapeName()
  Selection.Name = "Name of your choice"
End Sub

Obviously, replace “Name of your choice” to the actual name you want to give to the shape.

There you go. Now you can view and change a textbox’s name with ease. You could actually go above and beyond and create a toolbar or some type of graphic user interface to make this even easier! But that’s for another article.