CODEWITHSUNDEEP
excelvbaformscontrols
Andrés diego Menéndez
Andrés diego Menéndez

Reputation: 37

Is there a way to list all the textboxs, comboboxs, labels from a form that has multipages in a sheet?

Hi Stackoverflow community, I was wondering if there is a method to list all the controls (Labels, Textbox, ComboBox) from a form with multipage? This is because I need to update the script but it has around 30 pages and approximately 200 controls.

Upvotes: 1

Views: 77

Answers (2)

Tin Bum
Tin Bum

Reputation: 1491

This seems to be Ok with Pages I put it into a module then from anywhere in the form you call it with

ListAllMyControls Me, ""

Worked for me - I think - My form also had lots of pages and lots of controls - I didn't run a thorough check but this lists objects with a multi-level hierarchy like

Userform1.MultiPage1.Page1.Label1
Userform1.MultiPage1.Page1.TextBox1
Userform1.MultiPage1.Page2.Label1
Userform1.MultiPage1.Page2.TextBox1

The main routine follows

Public Sub ListAllMyControls(SrcObj As Object, dParentNames As String)
   Dim dCtr As Long, xObj As Object, ParentsWere As String, ParentsB4 As String
   
   ' List this Control
   ParentsWere = dParentNames
   dParentNames = IIf(dParentNames = "", "", dParentNames & ".") & SrcObj.Name
   Debug.Print dParentNames
   
   dCtr = 0
   On Error Resume Next
   dCtr = SrcObj.Controls.Count
   On Error GoTo 0
   
   If UCase(Left(SrcObj.Name, 9)) = "MULTIPAGE" Then
      For Each xObj In SrcObj.Pages
         ParentsB4 = dParentNames
         ListAllMyControls xObj, dParentNames
         dParentNames = ParentsB4
      Next xObj
   Else
      If dCtr > 0 Then
         ' List all my controls
         For Each xObj In SrcObj.Controls
            ParentsB4 = dParentNames
            ListAllMyControls xObj, dParentNames
            dParentNames = ParentsB4
         Next xObj
      Else
         dParentNames = ParentsWere
      End If
   End If  ' MultiPage
End Sub

Added comment - Problem is this seems to list objects twice when they're in a page. It lists them as Controls of the page and also as controls of the form the page is ultimately on.

Upvotes: 1

Variatus
Variatus

Reputation: 14383

Paste this code to the form's code sheet and link it to a button you might temporarily add for this purpose.

Private Sub GetControlNames()
    
    Dim Ctl As Object
    
    For Each Ctl In Me.Controls
        Debug.Print Ctl.Name, Ctl.Parent.Name
    Next Ctl
End Sub

At the click of the button all control names will be listed in the Immediate Pane. In my test the Parent.Name was the form's name but my hope is that it will show the page the control is on in your setup. You might then replace Me.Controls with Me.Controls([Page Name]).Controls and get the names of the controls on any page of your project. This idea may need a little tweaking because I didn't try it.

Upvotes: 0

Related Questions