VBA Code to get row number of a clicked button

Question

I am try to determine the row of a clicked command button automatically.

I am using the below code:

Private Sub CommandButton2_Click () 
   
    Dim b As Object, r As Integer 
    Set b = ActiveSheet.Buttons(Application.Caller) 
    With b.TopLeftCell 
        r= .row
    End With 
    MsgBox "Row Number " & r
End Sub 

But I keep getting an error message when I run the code saying "Run Time ERROR 1004 -Unable to get Buttons Property of the Worksheet class"

Can you please advise?

Answer 1

As @BigBen notes, you probably have an ActiveX button, so you can use:

Private Sub CommandButton2_Click () 
   MsgBox Me.CommandButton2.TopLeftCell.Row
End Sub 

If you have a lot of ActiveX buttons and you don't want to write a handler for each of then then you can use this approach: https://bettersolutions.com/excel/macros/vba-control-arrays.htm

You might find it easier though to use forms buttons instead (or just shapes) and a single "OnAction" sub using the Application.Caller approach.