Lots of similar buttons need to call a macro but need to find outwhich button was clicked
 

Hi

In my spreadsheet I've got about 20 buttons which need to call a
macro, but the macro code needs to be based on the row in which the
button was pressed. Is there a good way of finding out the current
row. Activecell doesn't change when you press the button.

I know I can in each of the click events of the buttons call the macro
and pass in an argument for the row number. Just thought there was a
better way.

I am happy to use labels, autoshapes or buttons if this affects a
suggestion.

Many thanks

Mike

Lots of similar buttons need to call a macro but need to find out
 

Buttons are part of controls which are really shapes. Shapes do not have
rows and columns but are positions by the left and top properties which are
in pixels. Cell also have Top and Left properties which can be used to
position shapes.


In this case I recommend hard coding the row number. Each button will have
its own click event and put the row number in the click function. You can
have each of the click events call a common routine like below

Private Sub OptionButton1_Click()
MyRow = 5
call commmonbutton(MyRow)
End Sub
Private Sub OptionButton1_Click()
MyRow = 6
call commmonbutton(MyRow)
End Sub
Private Sub OptionButton1_Click()
MyRow = 7
call commmonbutton(MyRow)
End Sub
sub commmonbutton(MyRow as Integer)
'put common code here
end sub
"Guabble" wrote:

Hi

In my spreadsheet I've got about 20 buttons which need to call a
macro, but the macro code needs to be based on the row in which the
button was pressed. Is there a good way of finding out the current
row. Activecell doesn't change when you press the button.

I know I can in each of the click events of the buttons call the macro
and pass in an argument for the row number. Just thought there was a
better way.

I am happy to use labels, autoshapes or buttons if this affects a
suggestion.

Many thanks

Mike

Lots of similar buttons need to call a macro but need to find out
 

Thanks for that. That's what I've done. I done the buttons as labels,
and then on the click event call the macro passing in the row number.

Cheers mate. Happy New Year to you!



On 7 Jan, 11:28, Joel wrote:
Buttons are part of controls which are really shapes. *Shapes do not have
rows and columns but are positions by the left and top properties which are
in pixels. *Cell also have Top and Left properties which can be used to
position shapes.

In this case I recommend hard coding the row number. *Each button will have
its own click event and put the row number in the click function. *You can
have each of the click events call a common routine like below

Private Sub OptionButton1_Click()
MyRow = 5
call commmonbutton(MyRow)
End Sub
Private Sub OptionButton1_Click()
MyRow = 6
call commmonbutton(MyRow)
End Sub
Private Sub OptionButton1_Click()
MyRow = 7
call commmonbutton(MyRow)
End Sub
sub commmonbutton(MyRow as Integer)
* 'put common code here
end sub



"Guabble" wrote:
Hi

In my spreadsheet I've got about 20 buttons which need to call a
macro, but the macro code needs to be based on the row in which the
button was pressed. *Is there a good way of finding out the current
row. *Activecell doesn't change when you press the button.

I know I can in each of the click events of the buttons call the macro
and pass in an argument for the row number. *Just thought there was a
better way.

I am happy to use labels, autoshapes or buttons if this affects a
suggestion.

Many thanks

Mike- Hide quoted text -

- Show quoted text -