Return text from a TextBox

[mdmackillop]

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?

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]

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?

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]

Thanks Ken,

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