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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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

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

--
Regards,
Tom Ogilvy

Gromit wrote in message
...
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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.

Gromit wrote:

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

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson

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.

--
Regards,
Tom Ogilvy


Gromit wrote in message
...
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Thanks very much Tom and Dave - sudden



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Thanks Dave and Tom...

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

Cheers,

Gromit



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/