[swensenj]

I am building a solution that will have many UserForms. I would like to write a script to get a list of the names of the UserForms. I've tried several options and have search the net with little success. The one example I did find looks something like:


Dim Obj As Object

For Each Obj In VBA.UserForms
Debug.Print Obj.name
End If
Next Obj


But there is never anything in the VBA.UserForms. Any suggestions would be appreciated.

 

[David M58]

Here's one way to get a list of UserForms in Excel.

Sub list_user_forms()
    Dim Obj As Object
    For Each Obj In ThisWorkbook.VBProject.VBComponents
        If Obj.Type = 3 Then
            Debug.Print Obj.Name
        End If
    Next Obj
End Sub

 

[swensenj]

Yes, this is one way to get them. Thank you. BUT... (and you knew it was coming :smile

In order to use this method you need to open your security options. If you will open Excel and select Tools->Macros->Security and then select the Trusted Publisher tab, you will see a check box titled "Trust access to Visual Basic Project". If the box is checked you can use this method, but if it is unchecked you get an error. Checking this box has been deemed a security risk by IS and allows for code interjection. (This is what I have been told, but have know personal experience how to do it or what can be done.)

So, the bottom line is, I need to find a different way, if it is even possible.

 

[David M58]

You're absolutely right about the security options. I certainly would be interested in an alternate way of getting UserForm information without having to change security settings, if anyone has come up with such a method.

 

[Glaswegian]

You can count Userforms using the Userforms collection - but this would only apply to loaded forms. I don't know of any other way, apart from the one David M58 outlined, to avoid the security settings.