Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
List Modules in a user form
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
List Modules in a user form
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) "Rob" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
List Modules in a user form
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 "Chip Pearson" wrote: 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) "Rob" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list box on user form | Excel Programming | |||
VBA & User Form modules vanish | Excel Programming | |||
User Form List Box | Excel Programming | |||
User Form / List Box | Excel Programming | |||
Creating a list box in a user form | Excel Programming |