Populate a ComboBox with the Subs of a Workbook

[SeniorNewbie]

Hello out there,
of course I know that with Alt+F8 there's an easy access to all subs of a workbook. Anyway I need a way to populate a combobox with alls subs, placed in modules, worksheets oder workbook. The userform will be a starting ponit for several cross-over tests with several workbooks and sheets.

Years ago Ifound a good solution in an other forum, which is unfortunately not longer existing. Any ideas?

THX a lot

Senior

 

[Muhammad_Usman]

To populate a ComboBox in a userform with the names of all subs (subroutines) in modules, worksheets, or the workbook itself, you'll need to use VBA code to search for and retrieve the names of these subs. Here's an example of how you can do this:

1. Create a UserForm in your VBA project if you haven't already.
2. Add a ComboBox control to your UserForm. You can do this by opening the UserForm in the VBA editor, then adding the ComboBox from the Toolbox.
3. In the code module for your UserForm (usually called "UserForm1" or similar), add the following code
   
   
   
   

   Private Sub UserForm_Initialize()
       Dim wb As Workbook
       Dim ws As Worksheet
       Dim mod As Object
       Dim i As Long
       
       ' Clear the ComboBox
       ComboBox1.Clear
       
       ' Add subs from the Workbook
       Set wb = ThisWorkbook
       For i = 1 To wb.VBProject.VBComponents.Count
           If wb.VBProject.VBComponents(i).Type = 1 Then ' Type 1 represents modules
               ComboBox1.AddItem wb.VBProject.VBComponents(i).Name
           End If
       Next i
       
       ' Add subs from Worksheets
       Set ws = ThisWorkbook.Sheets("YourSheetName") ' Change "YourSheetName" to the sheet name
       For i = 1 To ws.CodeName.VBProject.VBComponents.Count
           If ws.CodeName.VBProject.VBComponents(i).Type = 1 Then
               ComboBox1.AddItem ws.CodeName.VBProject.VBComponents(i).Name
           End If
       Next i
       
       ' Add subs from UserForm
       Set mod = UserForm1
       For i = 1 To mod.CodeModule.CountOfLines
           If Left(Trim(mod.CodeModule.Lines(i, 1)), 3) = "Sub" Then
               ComboBox1.AddItem Split(Split(Trim(mod.CodeModule.Lines(i, 1), "("))(0))(1)
           End If
       Next i
   End Sub

   
   This code initializes the ComboBox when the UserForm is loaded. It adds the names of subs from the Workbook, a specific Worksheet (replace "YourSheetName" with the actual sheet name), and the UserForm itself to the ComboBox.
   
   Make sure to adjust the code to your specific needs, including the sheet name and UserForm name. This code will provide you with a list of subs that you can select from in the ComboBox.