VBA Code to get row number of a clicked button

[amirkharkongor]

Hi,

I created multiple buttons through the following code :

Set Btn = ActiveSheet.Buttons.Add(bRowRng.Left, bRowRng.Top, bRowRng.Width, bRowRng.Height)
Btn.Caption = "Update"
Btn.OnAction = "Update"
Btn.Name = "BtnName" & BtnCnt


Now I want to write another code to get the row Number of the above buttons.

I tried the following solution available in this forum but i got an error "Unable to get the Buttons property of the Worksheet class"

Sub Mainscoresheet()
' Mainlineup Macro
Dim b As Object, RowNumber As Integer
Set b = ActiveSheet.Buttons(Application.Caller) ["Unable to get the Buttons property of the Worksheet class"]
With b.TopLeftCell
RowNumber = .Row
End With
MsgBox "Row Number " & RowNumber
End Sub

Kindly help me.

 

[Fluff]

How are you running the Mainscoresheet macro?

 

[amirkharkongor]

How are you running the Mainscoresheet macro?

I was testing the code directly.

Now I have assigned it to the button and it is working.

Thank you.

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[amirkharkongor]

Glad you sorted it & thanks for the feedback.

But now I am having another problem.

While using the following code I am getting a compile error expected function or variable.

Set Btn = ActiveSheet.Buttons.Add(bRowRng.Left, bRowRng.Top, bRowRng.Width, bRowRng.Height)
Btn.Caption = "Update"
Btn.OnAction = BtnUpdate [expected function or variable]
Btn.Name = "BtnName" & BtnCnt

calling the following function

Sub BtnUpdate()
rowNo = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
MsgBox "Test Row No. : " & rowNo
End Sub

 

[Fluff]

That needs to be in quotes.

 

[amirkharkongor]

That needs to be in quotes.

Thanks. It is working now

 

[Fluff]

You're welcome & thanks for the feedback.