Search code examples
excelvbabuttonparametersevaluate

How to assign a macro that uses parameter to a button in excel

A client requested that I add the buttons as shown in image 1 below.

enter image description here

There can be from 1 to 100 sets of buttons. The reason is unimportant to this question.

Seeing as they have practically identical functions I need to assign them but somehow have the row number available as a parameter or variable or something.

Private Sub Edit_CVR_Row(Btn_row As Integer)

At the generation of the buttons the row number is part of a for loop and known and this is how I assign the macro

With btn
    .OnAction = "'Edit_CVR_Row """ & Btn_row & "'"
    .Caption = "Edit"
    .Name = "Sheet1_" + Str(Btn_row) + "_Edit"  
End With

This is the error I get

Error 1

There is a similar solution that uses the Evaluate function with similar results.

Any help will be greatly appreciated.

Solution

  • activesheet.buttons(application.caller).topleftcell.row

    Thank you for this Rory, it worked.