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

Hmm...

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


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

In article
<7da6ac83-4d6b-4486-921a-(E-Mail Removed)>,
(E-Mail Removed) wrote:

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

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)

Do I need to simply add a reference?