List Modules in a user form

[Guest]

I am trying to create a user form that will display a selectable list of
available modules within a VBAproject in excel.

How do I return a list of modules?

Thanks

 

[Chip Pearson]

Rob,

You can use code like the following. In VBA, go to the Tools menu, choose
References, and scroll down to and check "Microsoft Visual Basic For
Applications Extensibility 5.3". This library defines the objects that make
up the components of a VBA Project.


Private Sub CommandButton1_Click()

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

End Sub

This is the Click event procedure for a button named CommandButton1. It
assumes your list box is named ListBox1. Change the names as you see fit.

See www.cpearson.com/Excel/VBE.aspx for much more information about using
code to manipulate objects and code in the VBA editor.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Thank you Chip. Your code example got me pointed in the right direction. The
code you supplied produced a list of all items (Forms, Sheets, etc...). The
following code returns only the Modules.

Dim VBComp As VBIDE.VBComponent
With lst_Modules
.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