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

On May 9, 5:39 pm, robotman <robot...@gmail.com> wrote:
> 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-

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.