Applying a list of macros into a combobox for user selection

[lordterrin]

Hi There,

I'm piecing together a few different macros that I have in an attempt to build a new one, and I'm running into a problem that I can't figure out.

I have a button on a worksheet that launches a UserForm to have the user select which workbook they want to run a given macro on. The code, here:

Private Sub CommandButton1_Click()
    wbName = Me.ComboBox1.Value
    Unload Me
    
    Sheets(1).Range("B6").Value = wbName
    
End Sub


Private Sub UserForm_Initialize()
    Dim wkb As Workbook
    Me.Label1.Caption = "Select Workbook"
        With Me.ComboBox1
             For Each wkb In Application.Workbooks
                .AddItem wkb.Name
             Next wkb
        End With


End Sub

works just fine. There are no issues with this.

Right below that box is another button that prompts the user to select which macro they want to run on the book they selected in the first box.



My second button, UserForm2, has the following code, which is NOT working properly:

Private Sub CommandButton2_Click()
    wbName = Me.ComboBox2.Value
    Unload Me
    
    Sheets(1).Range("B9").Value = wbName
        
    
End Sub


Sub ListOfMacros()
    On Error Resume Next '< error = reference already set
     'set reference to Microsoft Visual Basic for Applications
     'Extensibility 5.3
    ThisWorkbook.VBProject.References.AddFromGuid _
    "{0002E157-0000-0000-C000-000000000046}", 5, 3
     'now get the list of macros
    Call GetTheList
End Sub
 
Private Sub GetTheList()
    Dim N&, Count&, MyList(200), List$
    Dim Component As VBComponent
    For Each Component In ActiveWorkbook. _
        VBProject.VBComponents
        With Component.CodeModule
            Count = .CountOfDeclarationLines + 1
            Do Until Count >= .CountOfLines
                MyList(N) = .ProcOfLine(Count, _
                vbext_pk_Proc)
                Count = Count + .ProcCountLines _
                (.ProcOfLine(Count, vbext_pk_Proc), _
                vbext_pk_Proc)
                Debug.Print MyList(N)
                List = List & vbCr & MyList(N)
                If Count < .CountOfLines Then N = N + 1
            Loop
        End With
        N = N + 1
    Next
    
    Me.Label1.Caption = "Select Macro"
    With Me.ComboBox2
        For Each List In Application.Workbooks
            .AddItem List.Name
        Next List
    End With
    
    
'    MsgBox List, , "List of Macros"
End Sub

If I run the two subs ListofMacros and GetTheList outside of the userform, (in a module by themsleves), that I got verbatim off this forum - they work fine. They provide back to me the names of all the macros currently running in the active workbook. This is CLOSE to what I want - but instead of a msgbox, I want it to populate the combobox so that the user can select one.

Since the MsgBox is giving back the variable list, I assume that is the variable where the data is held - so I could use that in the combobox - but I'm getting something wrong.

The code to actually provide the list of macros is too complicated for me to understand line by line, but this part:

Me.Label1.Caption = "Select Macro"
    With Me.ComboBox2
        For Each List In Application.Workbooks
            .AddItem List.Name
        Next List
    End With

seems like it should be working. However, when I click the button to launch UserForm2, the combobox is empty, and I'm not sure where I'm going wrong.