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.