Is there a consistent way to get a sequential list of controls

[JKwan]

A user of mine designed a bunch of Textboxes, Checkboxes. I am wondering is there a consistent way of extracting the content from that sheet? She used the default names (Textbox1 to TextboxX). The problem is that there are so many of the Textboxes and Checkboxes (around 400 of them) and I don't want to map each one. I was thinking of walking all the controls on the sheet and write them out instead of mapping them one by one. Something like below:

Sub LoopControls()
    Dim OleObj As OLEObject

    For Each OleObj In ActiveSheet.OLEObjects
      If OleObj.progID = "Forms.TextBox.1" Then
            MsgBox OleObj.Object.Text
      End If
    Next OleObj
End Sub

Will the For Each statement always be consistent of returning the data? What if I rearrange the Textboxes, will the sequence change?

What about is there something like Tabstop where I can assign like with forms??

Is there a better solution that any of you experts can give me?

Thanks.

[Paul_Hossler]

Will the For Each statement always be consistent of returning the data? What if I rearrange the Textboxes, will the sequence change?

The For Each will return the controls in the order they were added (if I remember correctly) so physically rearranging them on the worksheet should not change the order

What about is there something like Tabstop where I can assign like with forms??

Don't think so

Is there a better solution that any of you experts can give me?

Suggestion would be to just For Each the OLEObjects, write .Name, .OLEType, .Caption, etc. to another worksheet and then sort

You could add a 'New Name' column to the WS and enter a more meaningful name, write a throw-away macro, and rename the OLE objects

[snb]

You can store all textboxes/checkboxes in 2 separate Arrays/Collections/Dictionaries.

In another procedure you can use those Arrays/Collections/Dictionaries.

Dim sp() As Object, sq() As Object

Sub M_snb()
    ReDim sp(Sheet1.OLEObjects.Count)
    sq = sp
    For Each it In Sheet1.OLEObjects
       Select Case TypeName(it.Object)
       Case "TextBox"
            Set sp(j) = it
           j = j + 1
        Case "CheckBox"
            Set sq(y) = it
           y = y + 1
        End Select
    Next
    
    ReDim Preserve sp(j - 1)
    ReDim Preserve sq(y - 1)
End Sub

Sub M_snb_001()
    For Each it In sp
       MsgBox it.Name
    Next
    
    For Each it In sq
       MsgBox it.Name
    Next
End Sub

[JKwan]

@Paul - Thanks for the info
@snb - I think your routine is a big time save!!

Thank you both!!!

[Toubkal]

Hi,
Here are 2 Subs:
1- ObjsList to display each object's name in column 6
2- ObjsValues to display each Textbox's name and Value.

call them both or separately

Hope this helps:

Option Explicit

Sub ObjsValues()
Dim ObjOle As OLEObject
Dim Rw
Dim Ws
Dim SrchCrit

Rw = 2    'just a row counter to write results in our sheet

Set Ws = ThisWorkbook.Worksheets("Feuil2")           ' because of my french version :)

SrchCrit = "TEXTBOX"                                             'search criteria here = TEXTBOX you can change it to what ever you look for . in CAPITAL pls

For Each ObjOle In Ws.OLEObjects
   If InStr(UCase(ObjOle.Name), SrchCrit) <> 0 Then      ' if object name matches SrchCrit
        Ws.Cells(Rw, 1) = ObjOle.Name                             ' Write Obj name
        Ws.Cells(Rw, 2) = ObjOle.Object.Value                   ' Write Obj value  !! for other objects like combobox, listbox, checkbox ... it will be different
        Rw = Rw + 1                                                        ' Next row
   End If
Next

End Sub

And

Sub ObjsList()
Dim ObjOle As OLEObject
Dim Rw
Dim Ws
Dim SrchCrit

Rw = 2                                                                      'just a row counter to write results in our sheet
Set Ws = ThisWorkbook.Worksheets("Feuil2")
For Each ObjOle In Ws.OLEObjects
        Ws.Cells(Rw, 6) = ObjOle.Name                           ' Write Obj name
        Rw = Rw + 1                                                      ' Next row
Next
End Sub

Toubkal.

[JKwan]

@Toubkal, Thanks - now, makes my life even easier.