Return text from a TextBox

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

Code:

---

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

---

Quote:

---

Originally Posted by 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?: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:

Code:

---

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,

Thanks Ken,

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