text box issue

[Turtleman10]

I need to read whats in all text boxe's on a spreadsheet. The Textboxes were created using excel not vba (insert/textbox). Im pretty sure its not an OLELbject. I tried using this code to find it and It did not see them. any help would be great

Sub textboxes()

Dim oleObj As OLEObject

For Each oleObj In ActiveSheet.OLEObjects
If oleObj.OLEType = xlOLEControl Then
If Left(oleObj.progID, 14) = "Forms.TextBox." Then
Set oleTxtbox = oleObj.Object
sCollect = sCollect & oleTextbox.Text
End If
End If
Next

End Sub

 

[zelgar]

Have you tried using the "Record Macro" to see what the code it creates to edit a few of the textboxes? See if that gives you some idea of the code Excel is using.

 

[SkipVought]

hi,

You need to be SURE!

I corrected your code

Sub textboxesZ()

    Dim oleObj As OLEObject, oleTxtbox As Object, sCollect As String
    
    For Each oleObj In ActiveSheet.OLEObjects
        If oleObj.OLEType = xlOLEControl Then
            If Left(oleObj.progID, 14) = "Forms.TextBox." Then
                Set oleTxtbox = oleObj.Object
                sCollect = sCollect & oleTxtbox.Text
            End If
        End If
    Next
    MsgBox sCollect
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

You can test the objects in the run-time. In the worksheet's module add code:

Sub test()
Dim objects1, objects2
Set objects1 = Me.Shapes
Set objects2 = Me.OLEObjects
End Sub

Add a breakpoint in the last line and run the code. Display the "Locals window" wgen the code breaks. See items in the wider shapes collection and in oleobjects collection. Check name, type and progid (oleobject) properties.



combo