Capture button name or row # during click?
On May 9, 5:39 pm, robotman 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-
|