Formula links in text boxes?

  • Thread starter Thread starter Gromit
  • Start date Start date
G

Gromit

I'm newly experimenting with VBA, so apologies if this is a stupid
question. Any help much appreciated!

I have a For - Next loop that creates a formula linking a cell in an
'input sheet' ("Key") to a cell in any one of 200 sheets. For cells,
this is no problem, using:

For i = 1 to 200
Worksheets(i).Range("c2").Formula = "=Key!B" & i
Next i

My problem comes when I try to do the same with some text boxes that
are in each of the 200 sheets. The 'Shape' object doesn't have a
'formula' method, so I can't figure out how to achieve the same effect
as clicking on the text box and entering the formula into the formula
bar. When I record this, I get the following curious result:

ExecuteExcel4Macro "FORMULA(""=Key!d & i"")"

This is performed on a selection, which is something I was trying to
avoid. If anyone could help me understand what's going on and how to
link the text box, I'd be very grateful!

Thanks again for any help!

G
 
It this is a textbox from the drawing toolbar, then this worked fine for me:

Activesheet.Textboxes("Text Box 13").Formula = "=Sheet1!I9"
 
Hi Tom,

Thanks, your solution worked great. But I don't understand how you
found out about the "Textboxes" object. It's not listed in the MS
online help, the MS VBA help, the Object browser or anywhere else that
I can find! Is there a whole world of similar objects out there that I
don't know about??

Thanks,

Graham
 
Next time you're in the Object browser:

Rightclick on one of the windows on the right hand side and select Show hidden
members.

You'll find a lot of controls from the forms toolbar there, too.
 
You have to select to view hidden objects in the object browser. Their is
no help.

These are the controls that were available in Xl5/xl95 (prior to xl97).
They are still supported, but not documented in the later versions.
 
Thanks Dave and Tom...

Beginning to realize what a curious beast this program is...

Cheers,

Gromit
 
Back
Top