Access VBA Userform Controls without Form instance

Question

I have a friend with a VBA project in Excel. This project has a lot of Forms that pop up and perform various functionality while the spreadsheet is being used. Some of them have complex Form_Initialize methods that rely on other things already existing (this is not a problem when the project is used as expected).

We are trying to print out the names of every control on every form within the application. Our problem is that the VBA.UserForms collection only contains forms that have already been instantiated, and we can't instantiate all the forms without their Form_Initialize methods executing.

For example:

For Each f In VBA.UserForms
    Debug.Print f.Name
    Debug.Print "----------------------"

    For Each c In f.Controls
        Debug.Print c.Name
    Next c
Next f

does nothing if no forms have been used/loaded. This code:

For Each c in frmConfig.Controls
    Debug.Print c.Name
Next c

First executes frmConfig.Form_Initialize(), then loops through the controls on the form printing their names. This crashes, as things that need to happen before this form is available have not happened.

Is it possible to get the names of the controls on a form WITHOUT instantiating the form (avoiding execution of frmConfig.Form_Initialize())?

Any help much appreciated!

have you thought of having a second (optional) constructor for initializing? — user2140173
– user2140173, Commented Apr 4, 2013 at 11:40
@mehow: Yes, that's been considered, but this is a one-off we want to do, and shifting ctor logic to a different function is nearly as time consuming as just opening the forms in design view and writing down all the control names manually... — Alex McMillan
– Alex McMillan, Commented Apr 4, 2013 at 11:48
And I'm still interested as to whether or not this is possible... — Alex McMillan
– Alex McMillan, Commented Apr 4, 2013 at 11:49
If its a one off then why cant you just place your code in a sub and call it at the end of all code. after files been used ( userform initialized ) you will have all your control names in the immediate window? or am i missing something here? — user2140173
– user2140173, Commented Apr 4, 2013 at 11:54

Siddharth Rout · Accepted Answer · 2013-04-04 12:03:01Z

2

Is this what you are trying?

Option Explicit

Sub FindObjects()
    Dim vbc As VBIDE.VBComponent
    Dim frm As Object
    Dim Ctrl As MSForms.Control

    For Each vbc In ThisWorkbook.VBProject.VBComponents
        If vbc.Type = vbext_ct_MSForm Then
            With VBA.UserForms
                On Error Resume Next
                Set frm = .Add(vbc.Name)
                Debug.Print "Found userform :" & vbc.Name
                If Err.Number = 0 Then
                    For Each Ctrl In frm.Controls
                        Debug.Print "Controls in Userform " & vbc.Name & _
                        " - " & Ctrl.Name
                    Next Ctrl
                End If
                On Error Go To 0
            End With
        End If
    Next vbc
End Sub

IMP:

Set reference to Microsoft Visual Basic For Applications Extensibility
In Excel options, set "Trust Access To the VBA project Object Model"

Screen Shot

enter image description here

FOLLOWUP

Since this is a one time thing, do this

Open VBA Project
Press CTRL + F
Do as shown in the screenshot below and then run the code.
Close the file without saving once you have got what you need

enter image description here

edited Apr 4, 2013 at 12:03

answered Apr 4, 2013 at 11:39

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Alex McMillan Over a year ago

Add a Private Sub UserForm_Initialize method containing Debug.Print "Hello" to one of your forms and you'll see that it appears in your output :( But thanks anyway!!

Siddharth Rout Over a year ago

I just noticed that you mentioned above that this is a one time thing. Is this correct? if yes, then I might have a solution for you...

Siddharth Rout Over a year ago

See the followup that I posted in the above post then :)

Alex McMillan Over a year ago

Brilliant! Why didnt I think of that?!? :) Could even do this with a COPY of the original spreadsheet, meaning no problems undoing it. Thanks!

Siddharth Rout Over a year ago

If I would have known that it was a one time thing, I would have suggested this long back :P

|

Collectives™ on Stack Overflow

Access VBA Userform Controls without Form instance

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related