Linking formulas to text boxes in VBA

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

Gromit

Hi,

I just posted this to the programmers section, but I think those folks
are into more serious things than this! 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
 
There are two different textboxes. One from the Drawing toolbar and one from
the ControlToolbox toolbar.

One of these may give you an idea to try:

Option Explicit
Sub testme()

Dim myTextBox As TextBox
Dim wks As Worksheet

Set wks = ActiveSheet

For Each myTextBox In wks.TextBoxes
myTextBox.Formula _
= wks.Cells(myTextBox.TopLeftCell.Row, "A").Address(external:=True)
Next myTextBox

End Sub

Sub testme1()

Dim OLEObj As OLEObject
Dim wks As Worksheet

Set wks = ActiveSheet

For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is msforms.TextBox Then
OLEObj.LinkedCell _
= wks.Cells(OLEObj.TopLeftCell.Row, "A").Address(external:=True)
End If
Next OLEObj

End Sub

The top version is for textboxes from the Drawing toolbar. The bottom is for
textboxes from the controltoolbox.

If you have follow up questions, tell us which one you're using.
 
Back
Top