List Modules in a user form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob - 17 Sep 2007 18:42 GMT

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

Reply to this Message

Chip Pearson - 17 Sep 2007 20:37 GMT

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.

Signature

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

>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

Reply to this Message

Rob - 20 Sep 2007 16:20 GMT

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

> Rob,
>
[quoted text clipped - 29 lines]
> >
> > Thanks

Reply to this Message