Check if a Macro is assigned

[FortyTwo]

Hello,

I've been assigned to work with a file that has changed hands many times. In this file there are multiple macros contained in multiple modules.

What I'd like to find out is which of these macros are actually being used by the file. Is there any way to find out which macros are assigned in a workbook without checking each individual button?

Thank you,

Fortytwo

 

[Andrew Poulsom]

Sorry no. The macros could be assigned to all sorts of objects, eg a Button, a Shape or a Toolbar Control. There is no way of finding out what object (if any) a macro is assigned to.

 

[Tom Urtis]

What I'd like to find out is which of these macros are actually being used by the file. Is there any way to find out which macros are assigned in a workbook without checking each individual button?

The big problem you will have is that there are a lot of ways to run macros, where a button would not be involved at all. And maybe by "button" you mean a command button from the control toolbox, which has a click even procedure, not a macro assigned to it.

If you are only interested in the Forms buttons on worksheets, and you want to check which macros are being used by those particular buttons and attack the situation that way, albeit in a very limited fashion, this would do what you literally say you want, but again, even if all those buttons were removed, it would not stop anyone from running a macro a different way, hence as you say still "being used by the file".

Sub Test1()
Dim btn As Button, wks As Worksheet
For Each wks In Worksheets
For Each btn In wks.Buttons
MsgBox btn.OnAction
Next btn
Next wks
End Sub

 

[Tom Urtis]

Sorry, too late to edit as I thought that just seeing macro names would not tell you much, so here's the worksheet and button name that goes with each assigned-to-Forms button macro:

Sub Test1()
Dim btn As Button, wks As Worksheet
For Each wks In Worksheets
For Each btn In wks.Buttons
MsgBox _
"Sheet name:" & vbTab & wks.Name & vbCrLf & _
"Button name:" & vbTab & btn.Name & vbCrLf & _
"Macro name:" & vbTab & btn.OnAction, , "Sheet button macros"
Next btn
Next wks
End Sub

 

[FortyTwo]

Thanks Tom. The code provided accomplished what I was attempting to do.

 

[MattRoy]

Sorry, too late to edit as I thought that just seeing macro names would not tell you much, so here's the worksheet and button name that goes with each assigned-to-Forms button macro:

Old thread, but I replaced "Buttons" by "Shapes" to include other objects that can have macro assigned (like Label, Textbox...). Shapes also include buttons.

Sub ListAllMacroUsage()
    Dim shp As Shape, wks As Worksheet
    For Each wks In Worksheets
        For Each shp In wks.Shapes
            If shp.OnAction <> "" Then
                Debug.Print "Sheet: " & wks.Name & vbTab & "Name: " & shp.Name & vbTab & "Macro: " & shp.OnAction
            End If
        Next shp
    Next wks
End Sub