Changing a formula inside a textbox - Excel 2007

B

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

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.
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top