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

This worked for me:

Sub Tester12()
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.

' change Shapes to TextBoxes
Set txtBox1 = ActiveSheet.TextBoxes("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 = theText & Selection.Characters(Start:=x, _
Length:=250).Text ' another type mismatch here
Next

Debug.Print theText
Debug.Print Len(theText)

End Sub


or without selecting:

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


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

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

Debug.Print theText
Debug.Print Len(theText)

End Sub

--
Regards,
Tom Ogilvy

"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

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

Thanks Tom your very good, I did pass the
Set txtBox1 = ActiveSheet.TextBoxes("Text Box 41")
instruction without error but when I'm trying to assign
the textBox text value to the variant variable "theText"
the type mismatch error pop-up again

I weird because I used your trick with the
theText = theText & txtBox1.Characters(Start:=x, _
Length:=250).Text

Unfortunately I can't explain or see what I did wrong
Jay


-----Original Message-----
This worked for me:

Sub Tester12()
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.

' change Shapes to TextBoxes
Set txtBox1 = ActiveSheet.TextBoxes("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 = theText & Selection.Characters(Start:=x, _
Length:=250).Text ' another type mismatch here
Next

Debug.Print theText
Debug.Print Len(theText)

End Sub


or without selecting:

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


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

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

Debug.Print theText
Debug.Print Len(theText)

End Sub

--
Regards,
Tom Ogilvy

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

copied the code out of a code module where it was extracting 680 characters
of text from text box 41. I can't say why it doesn't work for you?

tested in xl2000, sr1, but I doubt that going to XP would make a difference.

did you copy my code from the email and paste it into a module - then run
that? I assume x doesn't get greater than 32,767 (since you have x declared
as integer - however, I get an overflow error if that happens).

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


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

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

Debug.Print theText
Debug.Print Len(theText)

End Sub

--
Regards,
Tom Ogilvy

"Jay" wrote in message
...
Thanks Tom your very good, I did pass the
Set txtBox1 = ActiveSheet.TextBoxes("Text Box 41")
instruction without error but when I'm trying to assign
the textBox text value to the variant variable "theText"
the type mismatch error pop-up again

I weird because I used your trick with the
theText = theText & txtBox1.Characters(Start:=x, _
Length:=250).Text

Unfortunately I can't explain or see what I did wrong
Jay


-----Original Message-----
This worked for me:

Sub Tester12()
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.

' change Shapes to TextBoxes
Set txtBox1 = ActiveSheet.TextBoxes("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 = theText & Selection.Characters(Start:=x, _
Length:=250).Text ' another type mismatch here
Next

Debug.Print theText
Debug.Print Len(theText)

End Sub


or without selecting:

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


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

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

Debug.Print theText
Debug.Print Len(theText)

End Sub

--
Regards,
Tom Ogilvy