PDA

View Full Version : macro listbox



lior03
01-31-2006, 05:35 AM
hello
is there a way through vba to get the list of macros in a workbook.
i mean the list i get when i predd alt+f8.
maybe by using :
Application.Dialogs
thanks

matthewspatrick
01-31-2006, 07:19 AM
Moshe,

This is the same question you asked in:
http://www.vbaexpress.com/forum/showthread.php?t=6560

Patrick

malik641
01-31-2006, 07:34 AM
Hey Moshe,

I found this at http://www.cpearson.com/excel/vbe.htm and I modified it a little for more functionality. Tell me if it works :thumb

Option Explicit

Sub ListProcedures()
Dim VBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim Msg As String

For Each VBComp In ThisWorkbook.VBProject.VBComponents
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(VBComp.Name).CodeModule
Msg = Msg & VBComp.Name & vbCrLf
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
Msg = Msg & "-" & .ProcOfLine(StartLine, vbext_pk_Proc) & Chr(13)
StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Msg = Msg & vbCrLf
Next VBComp
MsgBox Msg
End Sub