Changing a formula inside a textbox - Excel 2007

[benspain_uk]

I am trying to find a way to change and view the formula inside a
textbox on a worksheet using VBA within Excel 2007.

In 2003 I use:

strButtonFormula = wsSheet.TextBoxes(strButtonName).Formula

and

wsSheet.TextBoxes(strButtonName).Formula = "=A1"

Nice and easy, and I have no issues.


However in 2007 - it doesn't like this code, and I can't find a way to
use "shapes" to get the same thing. I can get the text (i.e.
wsSheet.Shapes(strButtonName).TextFrame.Characters.Text), but not the
formula.

Also, when I record a macro in 2007, I get "ActiveCell.FormulaR1C1 =
"R1C1"", however this just doesn't work - it changes the last selected
cell, not the textbox!

Any ideas?????

 

[JE McGimpsey]

Hmm...

What do you mean XL2007 "doesn't like this code"?


It works for me, as long as the variables are assigned appropriately.

 

[benspain_uk]

When I try:

strButtonFormula = wsSheet.TextBoxes(strButtonName).Formula

I get

"run-time error '1004'
Unable to get the TextBoxes property of the Worksheet class"

(Both variables are declared as strings)

 

[benspain_uk]

Do I need to simply add a reference?