Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture button name or row # during click?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture button name or row # during click?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
capture listbox click | New Users to Excel | |||
Can you capture worksheet double click event from an Add-In???? | Excel Programming | |||
capture right mouse button click event on cell | Excel Programming | |||
Mouse Over Graph, Capture Information on Click(Double Click) | Excel Programming | |||
capture shift-double-click | Excel Programming |