Text Box Value

[Guest]

Two Possible answers to this one that will solve my problem... one from
Excel, one from VBA.

From Excel, is there a formula that will link a cell to a text box value?
-- If the value of "TextBox1" is "Test", is there a way to send this value
to cell A1.

From VBA, how do you refer to a text box from VBA?
-- I tried Sheets("Survey").TextBox(tbox) where tbox is a string with the
TextBox's name ("TextBox1" which changes with each text box). This returns an
error.

Thanks for your help

 

[Guest]

I figured out how to select the textbox using...
Sheets("Survey").Shapes("TextBox1").Select

and how to change the text in the box...
Sheets("Survey").Shapes("Text Box 125").Characters.Text = "test"

But how do I pull the value from the box...

 

[Guest]

abc = Sheets("Survey").Shapes("Text Box 125").Characters.Text

 

[Guest]

That returns a "Run-Time Error 438: Object doesn't support this property or
method"

 

[Guest]

Shoot me...

You have to use DrawingObjects

abc = Sheets("Survey").DrawingObjects("Text Box 125").Characters.Text

 

[Guest]

Demo'd from the immediate window (for textboxes from the drawing toolbar).
? activesheet.Textboxes("Text Box 1").Text
abcdefgh

Range("A1").Value = activesheet.Textboxes("Text Box 1").Text
? range("A1").Value
abcdefgh
Activesheet.Textboxes("Text Box 1").Text = " Why Hello there!"
? Activesheet.Textboxes("Text Box 1").Text & " " & Range("A1").Value
Why Hello there! abcdefgh

Manually, select the textbox

go to the formula bar and put in

=A1<enter>

Now they are linked.