How to copy text from a TextBox in a ActiveSheet to a variable
 

I think you just need to use the name of the text box to grab the contents
like this:

Dim x As String
x = TextBox1.Text
MsgBox "Text Box says " & x, vbInformation

Where 'TextBox1' is the name of your text box. I might be missing something
but this works for me.

Hope it helps.



"Jay" wrote in message
...
Even if the question seem simple at first I'm having
quite a problem solving it.

I have a TextBox in my ActiveSheet and trying to user VBA
to access the object and copy the text into a variable.
VBA is always returning the run-time error 13: type
mismatch. Here's my code:

Dim x As Integer
Dim txtBox1 As TextBox
Dim theText As Variant ' I've could put a string as well

ActiveSheet.Shapes("Text Box 41").Select

' to Set txtBox1 equal to the active sheet's TextBox
' this is the fist part of my problem I get a type
mismatch on that line.

Set txtBox1 = ActiveSheet.Shapes("Text Box 41")

'I've seen a different version on the Microsoft support
web site that is using: ActiveSheet.DrawingObjects("Text
Box 41") but my version of excel(excel XP) doesn't
recognize that method.

'I did try to resolve the problem by using the Selection
method with the following lines:

For x = 1 To Selection.Characters.Count Step 250
theText = Selection.Characters(Start:=x,
Length:=250).Text ' another type mismatch here
Next

So I'm open to suggestion if someone would know a way to
copy the text value of a TextBox into a variable