List Of Userforms And Modules

[Jaye7]

Can anyone please provide a script that lists all of the userforms in my file named Excel Template in Listbox1 and a script that lists all of the modules in the same workbook in listbox2.

I don't want to list the macros in the modules though, just the modules themselves.

Thanks

 

[Jaye7]

Anyone.

 

[xenou]

Chip Pearson has the best information I have seen on this kind of thing:
http://www.cpearson.com/excel/vbe.aspx

Not sure if it covers userforms or not, though.

 

[Jaye7]

Chip's site doesn't help, I have found a procedure for listing modules, sheets and userforms, but not one for just userforms.

the code for modules is

Dim VBComp As VBIDE.VBComponent
With ListBox1
.Clear
For Each VBComp In ThisWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_StdModule Then
.AddItem VBComp.Name
End If
Next VBComp
.ListIndex = 0
End With

the code for modules, forms and sheets is

Dim VBComp As VBIDE.VBComponent
With Me.ListBox1
.Clear
For Each VBComp In ThisWorkbook.VBProject.VBComponents
.AddItem VBComp.Name
Next VBComp
.ListIndex = 0
End With

But I want one that just lists the userforms only.

 

[pgc01]

Chip's site doesn't help, I have found a procedure for listing modules, sheets and userforms, but not one for just userforms.

...

But I want one that just lists the userforms only.

Hi Jaye

Maybe you missed it when you studied Chip's page?

VBComponent

A VBComponent is one object within the VBProject. A VBComponent is a code module, a UserForm, a class module, one of the Sheet modules, or the ThisWorkbook module (together, the Sheet modules and the ThisWorkbook module are called Document Type modules.. A VBComponent is of one of the following types, identified by the Type property. The following constants are used to identify the Type. The numeric value of each constant is shown in parentheses.

• vbext_ct_ClassModule (2): A class module to create your own objects. See Class Modules for details about classes and objects.
• vbext_ct_Document (100): One of the Sheet modules or the ThisWorkbook module.
• vbext_ct_MSForm (3): A UserForm. The visual component of a UserForm in the VBA Editor is called a designer.
• vbext_ct_StdModule (1): A regular code module. Most of the procedures on this page will work with these types of components.

 

[Jaye7]

Thanks PGC but I have very basic VBA skills so don't know how to incorporate that as a VBA script, can you please help with it, the other scripts I found on other threads.

 

[Jaye7]

Thanks PGC I managed to get the code working using the script that you provided. Much appreciated.

 

[pgc01]

Hi. I was coming back to this thread but I see you already solved the problem, which is great. I'm glad it's solved.