Macro Name List

[JuniorASA]

Right now, I have a worksheet with lots of macro (around 20)

Is there any code such that I could use a macro to list the names of all macro in the sheet automatically??

Many Thanks,

[johnske]

Here's one... Option Explicit

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 As Long, Count As Long, MyList(200), List As String
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)
List = List & vbCr & MyList(N)
If Count < .CountOfLines Then N = N + 1
Loop
End With
N = N + 1
Next
MsgBox List, , "List of Macros"
End Sub

[lucas]

I see John has already posted here but I am going to attach a file that John shared some time back that I have found useful for just such cases as this...

[JuniorASA]

Thanks, however, I cannot run this macro.

Is there any special component for me to install in order to run this macro??

Many Thanks,

[lucas]

I cannot run this macro.
I'm gonna have to get Gerry after you(Inside joke for the regulars of the forum-from the word forum). What does that mean? Did you get a security warning when you opened the workbook or did you get an error when you ran it? If so what was the error?

[lucas]

You will also have to go to tools-macro-security
click on the trusted Publishers tab at the top
be sure the box marked "Trust access to visual basic project" is checked