VBA code to find macros associated with command buttons in an s/s

[peng.gong]

Given a sheet containing many command buttons, how can I find the
macros/subroutines associated with those buttons? (i want to do it in
vba code, rather than right click each button and select "assign
macro") Thanks a lot.

 

[Joel]

A button has a name and a Caption which may be different. this code get both
name and caption.

Sub Getbutton()
For Each but In ActiveSheet.Shapes
If Left(but.Name, 13) = "CommandButton" Then
MsgBox OLEObjects(but.Name).Object.Caption
End If
Next but
End Sub

 

[Dave Peterson]

First, if you really meant commandbuttons (those are from the control toolbox
toolbar), then you don't assign the macro.

If you doubleclick on one of these commandbuttons while in design mode, you'll
see that it has an event associated with it:

Option Explicit
Private Sub CommandButton1_Click()
End Sub

If you meant buttons from the Forms toolbar, you could use code like:

Option Explicit
Sub testme()

Dim myBTN As Button

For Each myBTN In ActiveSheet.Buttons
If myBTN.OnAction = "" Then
MsgBox "No macro assigned to " & myBTN.Name
Else
MsgBox myBTN.OnAction & vbLf & "is assigned to " & myBTN.Name
End If
Next myBTN

End Sub