Creating a list of all controls on all forms

[HighAndWilder]

Hi

Whilst I am quite familiar with creating a list of controls and properties for a form by using code in the forms
code module I cannot find a way of doing this for all forms.

If I correct in thinking that the userforms collection only includes loaded forms do I have to load all
forms and loop through the controls in each one in turn?

A pointer in the right direction would be appreciated.

Thanks

 

[Logit]

.
https://bytes.com/topic/access/answ...orms-list-all-form-properties-each-form-found

 

[HighAndWilder]

Hi.

Thanks for your reply but that link seems to link to an Access topic. Do you know of a similar Excel one.

HignAndWilder

 

[Logit]

.

Option Explicit


Sub ListControls()
    Dim lCntr As Long
    Dim aCtrls() As Variant
    Dim ctlLoop As MSForms.Control


     'Change UserForm Name In The Next Line
    For Each ctlLoop In UserForm1.Controls
        lCntr = lCntr + 1: ReDim Preserve aCtrls(1 To lCntr)
        'Gets Type and name of Control
        aCtrls(lCntr) = TypeName(ctlLoop) & ":" & ctlLoop.Name
    Next ctlLoop
     'Change Worksheet Name In The Next Line
    Worksheets("Sheet1").Range("A1").Resize(UBound(aCtrls)).Value = Application.Transpose(aCtrls)
End Sub

 

[Norie]

Here's some very simple, totally without error handling, code that will list all the controls in all the userforms in the active workbook.

Sub ListForms()
Dim vbproj As Object
Dim vbcomp As Object

    Set vbproj = ActiveWorkbook.VBProject
    
    For Each vbcomp In vbproj.vbcomponents
        If vbcomp.Type = 3 Then
               ListControls vbcomp
        End If
    Next vbcomp
End Sub

Sub ListControls(vbformcomp As Object)
Dim ctl As Object
    
    Debug.Print vbformcomp.Name
    
    For Each ctl In vbformcomp.designer.Controls
        Debug.Print vbTab & ctl.Name & vbTab & TypeName(ctl)
    Next ctl
    
End Sub

 

[HighAndWilder]

Thanks Norie

That is just what I was after.

I now need to loop through all the properties of each control but after trying to work out the syntax I am stuck again.

I will post another question.

HighAndWild