UserForm List Available Macros

[alexjf]

I'm trying to make a macro that will allow me to list and apply any available macro to any available worksheet/workbook. The below code already allows me to see the available worksheets/workbooks but I don't know how to update the language to include the available macros (both from my personal file and any additional macros from already open workbooks).

Option Explicit

' ComboBox1 => Displays workbooks
' ComboBox2 => Displays worksheets

Private Sub CommandButton1_Click()
    Dim oWb As Workbook, oSh As Worksheet
    
    Set oWb = Workbooks(ComboBox1.Value)
    Set oSh = oWb.Sheets(ComboBox2.Value)
    oSh.Activate
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Dim oWb As Workbook
    For Each oWb In Workbooks
        ComboBox1.AddItem oWb.Name
    Next oWb
    ComboBox1.ListIndex = 0
End Sub

Private Sub ComboBox1_Change()
    Dim oWb As Workbook, oSh As Worksheet
    
    ComboBox2.Clear
    Set oWb = Workbooks(ComboBox1.Value)
    For Each oSh In oWb.Sheets
        ComboBox2.AddItem oSh.Name
    Next oSh
    ComboBox2.ListIndex = 0
End Sub

 

[smozgur]

The following could be a starter, but you need to adjust some settings.

1- Add the "Microsoft Visual Basic for Applications Extensibility" reference (VBE->Tools->References)
2- Excel->Options->Trust Center->Trust Center Settings->Macro Settings->Trust Access to the VBA project object module (This could be somewhere else if you are using a version before 2007 - I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)).

And the update for the ComboBox1_Change event procedure could be as follows. (Quick adaptation from the related Microsoft Learn page. Refer to it for more info).

Private Sub ComboBox1_Change()
    Dim oWb As Workbook, oSh As Worksheet
    Dim oVbP As VBIDE.VBProject
    Dim oVbC As VBIDE.VBComponent
    Dim oVbCode As VBIDE.CodeModule
    Dim ILine As Integer
    Dim sProcName As String
   
    ComboBox2.Clear
    Set oWb = Workbooks(ComboBox1.Value)
    For Each oSh In oWb.Sheets
        ComboBox2.AddItem oSh.Name
    Next oSh
    ComboBox2.ListIndex = 0
   
    ComboBox3.Clear
    Set oVbP = oWb.VBProject
    For Each oVbC In oVbP.VBComponents
        ' The conditional is used to get only standard modules
        If oVbC.Type = vbext_ct_StdModule Then
            Set oVbCode = oVbC.CodeModule
            ILine = 1
            Do While ILine < oVbCode.CountOfLines
                sProcName = oVbCode.ProcOfLine(ILine, vbext_pk_Proc)
                If sProcName <> "" Then
                    ComboBox3.AddItem oVbC.Name & vbTab & sProcName
                    ILine = ILine + oVbCode.ProcCountLines(sProcName, vbext_pk_Proc)
                Else
                    ILine = ILine + 1
                End If
            Loop
        End If
    Next oVbC
End Sub

 

[alexjf]

I'm trying to add the code but it keeps giving me errors (after adding the reference you noted). Is there a way to modify the existing code to add a 3rd combobox to ensure it continues functioning? I'd certainly appreciate having cleaner code but I don't want to make you submit back and forths to getting it working (I appreciate you providing it).