Modify Textbox on Chart from Code

[Steve Haack]

I have a chart that I would like to modify from code. I am able to modify the
Text of a textbox with the following:

Set chrtSiteChart = ActiveSheet.Shapes("Sites").Chart
chrtSiteChart.Shapes("TextBox 2").TextFrame.Characters.Text = "SomeText"

But, what I also need to do, and cannot find the sytanx for, is to modify
the Cell that some TextBoxes are linked to. I am able to use this:

chrtActiveChart.TextBoxes(4).Formula = "='" & strTemp & "'!$N$34"

But there are quite a few charts that have several TextBoxes each, and I
don't really want to take the time to trial and error through them all to
find the Item ID for each one.

I was hoping to just use code to choose them by name and change their
Formula property, something like:

chrtActiveChart.Shapes("TextBox 2").Formula= strTemp

Anyone know how to do that?

Thanks,
Steve

 

[Peter T]

Change

chrtActiveChart.Shapes("TextBox 2").Formula= strTemp to
chrtActiveChart.TexBoxes("TextBox 2").Formula= strTemp

I guess you are using Excel 2007, in earlier versions default textbox names
includes a space "Text Box #"

Regards,
Peter T