Is it possible to list all macros that are assigned to shapes?

[tonywatsonhelp]

Hi Everyone,
I have a large document and I'm trying to do a bit of a clean up,
I was woundering if there was a macro that could give me a list of all macros that are assigned to a shape within the document?

I use shapes as buttons so this would help me see which macros are currently used buy a button?

If possible this would be perfect
Create a new sheet
Column A show Button Name,
Column B show sheet that buttons on
Column C macro name


anything would be a great help

Thanks

tony

 

[Domenic]

The following macro assumes that an Excel workbook is active, and lists all macros assigned to shapes within the active workbook...

Option Explicit

Sub ListMacrosAssignedToShapes()

    Dim list() As String
    Dim sht As Object
    Dim shp As Object
    Dim temp As Variant
    Dim cnt As Long
   
    ReDim list(1 To 100, 1 To 3)
   
    cnt = 0
    For Each sht In ActiveWorkbook.Sheets
        For Each shp In sht.Shapes
            If Len(shp.OnAction) > 0 Then
                cnt = cnt + 1
                list(cnt, 1) = shp.Name
                list(cnt, 2) = sht.Name
                list(cnt, 3) = shp.OnAction
                If cnt Mod 100 = 0 Then
                    temp = Application.Transpose(list)
                    ReDim Preserve temp(1 To 3, 1 To cnt + 100)
                    list = Application.Transpose(temp)
                End If
            End If
        Next shp
    Next sht
   
    If cnt > 0 Then
        Worksheets.Add
        Range("A1:C1").Value = Array("Button Name", "Sheet Name", "Macro Name")
        Range("A2").Resize(UBound(list, 1), UBound(list, 2)).Value = list
    Else
        MsgBox "No shapes with macros found!", vbExclamation
    End If
   
End Sub

Hope this helps!

 

[tonywatsonhelp]

This is excellent, thank you so much

 

[Domenic]

That's great, you're very welcome, cheers!

 

[Tom.Jones]

@Domenic,

I would like to ask you, if your code can be modified, so that, when clicking in a cell in column C (where the names of the VBA codes are listed) to open the respective module (not to run it).
Thank you.

 

[Domenic]

@Domenic,

I would like to ask you, if your code can be modified, so that, when clicking in a cell in column C (where the names of the VBA codes are listed) to open the respective module (not to run it).
Thank you.

The following event handler is executed whenever a cell in Column C is double-clicked. Note that it assumes that the first row in Column C contains a header. Also, note that the code must be placed in the code module for your sheet. So you'll need to right-click the sheet tab, select View Code, and then copy/paste the code into the code module.

The code loops through each module within the workbook, and searches for a macro within the module for the name found in the double-clicked/target cell. If found, it opens the Visual Basic Editor, if not already opened, and then it shows the code pane for that module. If not found, it displays a message accordingly. Note that the code searches for an exact match and is not case-sensitive. However, these settings can be changed as desired.

Also, you'll need to allow access to the VBA project object module . . .

File >> Options >> Trust Center >> Trust Center Settings >> Developer Macro Settings >> select Trust access to the VBA project object model

Here's the code...

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Not Intersect(Target, Columns("C")) Is Nothing Then
        If Target.Row > 1 Then
            If Len(Target) > 0 Then
                Cancel = True
                Dim vbComp As Object
                Dim sl As Long, sc As Long, el As Long, ec As Long
                Dim found As Boolean
                For Each vbComp In ThisWorkbook.VBProject.VBComponents
                    With vbComp.CodeModule
                        found = .Find(Target:=Target.Value, StartLine:=sl, StartColumn:=sc, EndLine:=el, EndColumn:=ec, wholeWord:=True, MatchCase:=False, PatternSearch:=False)
                        If found Then
                            Application.VBE.MainWindow.Visible = True
                            .CodePane.Show
                            Exit Sub
                        End If
                    End With
                Next vbComp
                MsgBox Target.Value & " not found!", vbExclamation
            End If
        End If
    End If
 
End Sub

Hope this helps!

 

[Tom.Jones]

Hi Domenic,

Tried your code (put in sheet module), nothing happens, on double click in column C. Tried with new file and with my file. What I do wrong?
Please, can you tell me whats wrong?
Thank you.

 

[Domenic]

My apologies, somehow I went off track, and didn't give you what you actually asked for. So I have amended my post. Let me know if you have any questions or run into any problems.

Cheers!

 

[Tom.Jones]

Thanks for reply.

Domenic,

Because the first code writes in column C, the file name and then the module name, the BeforeDoubleClick event gives the message that that module was not found.
I deleted the file name from column C and when I double-click in one of the cells, the respective module opens.
How can I change the VBA code "ListMacrosAssignedToShapes" so that it no longer writes the file name in column C.
Thank you.

 

[Domenic]

Oh, I didn't know that you would be using "ListMacrosAssignedToShapes" together with the BeforeDoubleClick event handler. In any case, to exclude the filename from the macro names, simply replace . . .

list(cnt, 3) = shp.OnAction

with

list(cnt, 3) = Mid(shp.OnAction, InStr(1, shp.OnAction, "!") + 1)

Cheers!