List contents of modules in ListBox

[Tom.Jones]

Howdy,

How can I list in the ListBox or TextBox (in the UserForm) the modules from a .xlsm file
In ListBox1, list the modules and in the ListBox2 or TextBox, the contents of the selected module in Listbox1.
Is that possible?
I have seen on Youtube or forums, that I can list the modules in a listBox (I'm looking to see if the VBA code is given), but I did not find the listing of the modules.

Thank you.

 

[footoo]

Not exactly what you asked for but here's some code that lists the modules and the macros in ListBox1 on UserForm1 (rather than separately on 2 listboxes):

Sub List_Modules_Macros()
Dim sourceWB$: sourceWB = "Whatever.xlsm" 'The name of the workbook containing the macros to be listed
Dim VBComp As VBComponent, VBCodeMod As CodeModule, StartLine&
Unload UserForm1
For Each VBComp In Workbooks(sourceWB).VBProject.VBComponents
    If VBComp.Type = vbext_ct_StdModule Then
        Set VBCodeMod = Workbooks(sourceWB).VBProject.VBComponents(VBComp.Name).CodeModule
        UserForm1.ListBox1.AddItem VBCodeMod
        With VBCodeMod
            StartLine = .CountOfDeclarationLines + 1
            Do Until StartLine >= .CountOfLines
                 UserForm1.ListBox1.AddItem .ProcOfLine(StartLine, vbext_pk_Proc)
                 StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, vbext_pk_Proc), vbext_pk_Proc)
            Loop
            UserForm1.ListBox1.AddItem "" 'Delete this line if a space between modules is not required
        End With
    End If
Next
UserForm1.Show vbModeless
End Sub

 

[Tom.Jones]

Excellent. Thanks footoo,

Now I would like to click on one of the listed modules in listbox1 to display the content of the module (code lines) in another listbox or textbox I same userform.
Can this happen?

Thanks a lot.

 

[Tom.Jones]

Need help.

 

[footoo]

Put in the UserForm code module :

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
' Declare variables to access the Excel workbook.
Dim objXLApp As Excel.Application
Dim objXLWorkbooks As Excel.Workbooks
Dim objXLABC As Excel.Workbook


' Declare variables to access the macros in the workbook.
Dim objProject As VBIDE.VBProject
Dim objComponent As VBIDE.VBComponent
Dim objCode As VBIDE.CodeModule


' Declare other miscellaneous variables.
Dim iLine As Integer, line%
Dim sProcName As String
Dim pk As vbext_ProcKind


' Open Excel, and open the workbook.
Set objXLApp = New Excel.Application
Set objXLWorkbooks = objXLApp.Workbooks
Set objXLABC = objXLWorkbooks.Open("d:\Documents\Whatever.xlsm")


' Empty the list box.
UserForm1.ListBox1.Clear


' Get the project details in the workbook.
Set objProject = objXLABC.VBProject


iLine = 1
line = 1


' Iterate through each component in the project.
For Each objComponent In objProject.VBComponents


    ' Find the code module for the project.
    Set objCode = objComponent.CodeModule
    
    ' Scan through the code module, looking for procedures.
    Do While iLine < objCode.CountOfLines
        sProcName = objCode.ProcOfLine(iLine, pk)
        If sProcName = UserForm1.ListBox1.Value Then
            Do
                UserForm1.ListBox2.AddItem objCode.Lines(line, 1)
                line = line + 1
                If line >= objCode.ProcCountLines(sProcName, pk) + 1 Then Exit For
           Loop
        Else
            ' This line has no procedure, so go to the next line.
            iLine = iLine + 1
        End If
    Loop
    Set objCode = Nothing
    Set objComponent = Nothing
Next
UserForm1.Show vbModeless


Set objProject = Nothing
' Clean up and exit.
objXLABC.Close
objXLApp.Quit
End Sub

Above code adapted from here : https://docs.microsoft.com/en-us/office/troubleshoot/retrieve-macro-name-in-excel-using-vb

 

[footoo]

Correction.
Delete the following :

' Empty the list box.
UserForm1.ListBox1.Clear

 

[footoo]

Revised (might still be problems) :

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
' Declare variables to access the Excel workbook.
Dim objXLApp As Excel.Application
Dim objXLWorkbooks As Excel.Workbooks
Dim objXLABC As Excel.Workbook


' Declare variables to access the macros in the workbook.
Dim objProject As VBIDE.VBProject
Dim objComponent As VBIDE.VBComponent
Dim objCode As VBIDE.CodeModule


' Declare other miscellaneous variables.
Dim iLine As Integer, line%
Dim sProcName As String
Dim pk As vbext_ProcKind


' Open Excel, and open the workbook.
Set objXLApp = New Excel.Application
Set objXLWorkbooks = objXLApp.Workbooks
Set objXLABC = objXLWorkbooks.Open("d:\Documents\Whatever.xlsm")


' Empty the list box.
UserForm1.ListBox2.Clear


' Get the project details in the workbook.
Set objProject = objXLABC.VBProject
' Iterate through each component in the project.
For Each objComponent In objProject.VBComponents
    line = 1
    ' Find the code module for the project.
    Set objCode = objComponent.CodeModule
    iLine = objCode.CountOfDeclarationLines + 1
    
    ' Scan through the code module, looking for procedures.
    Do While iLine < objCode.CountOfLines
        sProcName = objCode.ProcOfLine(iLine, pk)
        If sProcName = UserForm1("ListBox1").Value Then
            Do
                UserForm1.ListBox2.AddItem objCode.Lines(iLine, 1)
                iLine = iLine + 1
                line = line + 1
                If line > objCode.ProcCountLines(sProcName, pk) Then Exit For
            Loop
        Else
            ' This line has no procedure, so go to the next line.
            iLine = iLine + 1
        End If
    Loop
    Set objCode = Nothing
    Set objComponent = Nothing
Next
UserForm1.Show vbModeless


Set objProject = Nothing
' Clean up and exit.
objXLABC.Close
objXLApp.Quit
End Sub

 

[Tom.Jones]

Thank you footoo,

Although I've tested VBA several times, I could not get past the warning "Microsoft Excel is waiting for another application to complete an OLE action."
and excel is blocking.


I do not know if it matters: Windows 10 on 64 bits and office 365 on 64 bits.

 

[footoo]

It's working for me without error.
Have a look here : https://www.google.com/search?q="Mi....69i57j0l5.14415j0j8&sourceid=chrome&ie=UTF-8

 

[footoo]

Try replacing this :

Set objXLABC = objXLWorkbooks.Open("d:\Documents\Whatever.xlsm")

With this :

Dim path$, wb$, x%path = "D:\Documents\" 'Change path as required
wb = "Whatever.xlsm" 'Change name as required
Application.EnableEvents = False
On Error Resume Next
x = Len(Workbooks(wb).Name)
On Error GoTo 0
If x = 0 Then
    Set objXLABC = Workbooks.Open(path & wb)
Else
    Set objXLABC = Workbooks(wb)
End If
Application.EnableEvents = True