Assign Formula to Chart Sheet Text Box (migrating from Excel 2003 to Excel 2007)

[greenchile]

Hi,

I have some code (at the end of this message) that worked with Excel 2003 to assign a formula to a text box on a chart sheet. The code does not work with Excel 2007 and from the new Excel Object Model...I cannot find references to formulas with shape objects.

The overall purpose of my code is to allow the user to automatically generate a number of chart sheets with scatter plots and formatted title blocks. The title blocks contain information on the figure number, figure title, project, date, etc that is ready to be included in a report. The text boxes on the chart sheets are linked to cells on a "Figure Information" worksheet allowing the user to enter and update information for all charts from one worksheet.

[vba]
'Figure Number
linkform(1) = "'" & "Figure Information" & "'" & "!$"
linkform(2) = NumbertoLetter(figinfo + 1)
linkform(3) = "$5"
ActiveChart.Shapes("Text Box 2").Select
tbzorder = ActiveChart.Shapes("Text Box 2").ZOrderPosition
finallink = Join(linkform, "")
ActiveChart.TextBoxes(tbzorder).Formula = finallink

[/vba]

Any suggestions? Thanks!

greenchile

[Bob Phillips]

Can you post a sample workbook to save have us having to try and simulate what you have done?

[JonPeltier]

There is no "=" in your formula.
No need to reference by ZOrder, in fact, that may not be reliable.

Try this syntax:

ActiveChart.TextBoxes("Text Box 2").Formula = "=Sheet1!$B$3"