PDA

View Full Version : Solved: Return text from a TextBox



mdmackillop
10-26-2004, 01:01 PM
To help with this question http://www.vbaexpress.com/forum/showthread.php?t=1200

Can anyone give the code to return the text from a Textbox in Excel 2003?:help

2000 works with the following

Sub TextBoxText()
Search = InputBox("Enter Search Term")
Set MyWS = ActiveWorkbook.Sheets("Sheet1")
test = False
'Check the values in the Text Boxes here
For i = 1 To MyWS.Shapes.Count
' Debug.Print MyWS.Shapes(i).Name
BoxText = MyWS.Shapes(i).TextFrame.Characters.Text
If Left(MyWS.Shapes(i).Name, 4) = "Text" Then
If InStr(1, BoxText, Search) > 0 Then
test = True
MsgBox test
End If
End If
Next
End Sub

Ken Puls
10-26-2004, 02:52 PM
To help with this question http://www.vbaexpress.com/forum/showthread.php?t=1200

Can anyone give the code to return the text from a Textbox in Excel 2003?:help

Hi Malcolm,

I couldn't make your code fire in 2003, as the "characters" method just wouldn't work with the textbox I created.

I'm curious though, if you know how the op created the textbox? I pulled some code from MSDN which creates a textbox and you can manipulate the characters with that.

I was, however, successful pulling the text from my textbox using this:

Dim ctrl As OLEObject
For Each ctrl In ActiveSheet.OLEObjects
Select Case ctrl.progID
Case Is = "Forms.TextBox.1"
MsgBox ctrl.Object.Text
End Select
Next ctrl

I created my textbox with the Control Toolbox, and I'm wondering if your op did too. (ActiveX object vs Forms maybe?)

HTH,

mdmackillop
10-26-2004, 03:18 PM
Thanks Ken,

Can you assist with this question, as I'm stuck for any testing.