Getting a list of UserForms in Excel

8472 Views 4 Replies 3 Participants Last post by Glaswegian, Jun 15, 2008

swensenj

Discussion Starter · Jun 14, 2008

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.

Save

Status: Not open for further replies.

1 - 5 of 5 Posts

David M58

· #2 · Jun 14, 2008

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

Code:

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

Save

swensenj

Discussion Starter · #3 · Jun 14, 2008

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.

Save

David M58

· #4 · Jun 14, 2008

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.

Save

Glaswegian

· #5 · Jun 15, 2008

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.

Save

1 - 5 of 5 Posts

Status: Not open for further replies.

Top Contributors this Month