View Full Version : [SOLVED:] Is there a consistent way to get a sequential list of controls
JKwan
10-25-2018, 09:03 PM
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
10-26-2018, 07:34 AM
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
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
10-26-2018, 10:27 AM
@Paul - Thanks for the info
@snb - I think your routine is a big time save!!
Thank you both!!!
Toubkal
10-26-2018, 11:31 AM
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
10-26-2018, 12:35 PM
@Toubkal, Thanks - now, makes my life even easier.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.