Capture button name or row # during click?

[robotman]

I have a worksheet where I have a created a button in column A of
every row.

When the user presses the button for a particular row, I need to know
what row the user pressed the button in so I can take appropriate
action for that row.

Any ideas?!

Thanks.

John

__ WHAT DIDN'T WORK __

I'll include my failed approach but hopefully there's some simple
approach that I'm not thinking of...

When I create the buttons, I change the name to incorporate the row
number, but I can't figure out how to capture the row # based on which
button was pressed. I've tried passing a parameter to the sub with
the ".OnAction" parameter, but that doesn't work.

Example code to create button for 10 rows:

For Loop = 1 To 10
With ActiveSheet.Buttons.Add(Cells(Loop, 1).Left + 3,
Cells(Loop, 1).Top, 30, 10)
.Characters.Text = "Toggle"
.Name = Loop ' Name the button the Loop (i.e. Row #)
.OnAction = "Toggle_Row_Selection " & Loop ' Can't pass
the Row # here
End With
Next AnimalLoop

 

[JW]

I have a worksheet where I have a created a button in column A of
every row.

When the user presses the button for a particular row, I need to know
what row the user pressed the button in so I can take appropriate
action for that row.

Any ideas?!

Thanks.

John

__ WHAT DIDN'T WORK __

I'll include my failed approach but hopefully there's some simple
approach that I'm not thinking of...

When I create the buttons, I change the name to incorporate the row
number, but I can't figure out how to capture the row # based on which
button was pressed. I've tried passing a parameter to the sub with
the ".OnAction" parameter, but that doesn't work.

Example code to create button for 10 rows:

For Loop = 1 To 10
With ActiveSheet.Buttons.Add(Cells(Loop, 1).Left + 3,
Cells(Loop, 1).Top, 30, 10)
.Characters.Text = "Toggle"
.Name = Loop ' Name the button the Loop (i.e. Row #)
.OnAction = "Toggle_Row_Selection " & Loop ' Can't pass
the Row # here
End With
Next AnimalLoop

Never name a variable as one of VBAs reserved words, such as Loop. In
the example below, I renamed Loop to L. Also, in the OnAction
property, you need to assign that to a separate sub routine. You can
capture which button was clicked by using Application.Caller.

Sub foo()
For L = 1 To 10
With ActiveSheet.Buttons.Add(Cells(L, 1).Left + 3, Cells(L,
1).Top, 30, 10)
.Characters.Text = "Toggle"
.Name = L ' Name the button the Loop (i.e. Row #)
.OnAction = "toggler"
End With
Next
End Sub

Sub toggler()
MsgBox Application.Caller
End Sub

HTH
-Jeff-

 

[robotman]

Application.Caller works. I was stumped on this one.

Thank you!

John

P.S. I was just using "Loop" as an example variable, but thanks for
the reminder when posting sample code.