Select the cell that a button is in [Solved/Closed]

Vernoman - May 24, 2011 at 12:49 PM - Latest reply:  Vernoman
- May 24, 2011 at 04:43 PM
Hello,

I want to run a macro using a button in Excel 2010 without regard as to the location of the current cell.

In other words, when I click a button, the first thing I need the macro to do is select the current cell where the button resides. Then I can perform actions on that specific row.

I hope this question makes sense.

See more 

4 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 24, 2011 at 01:05 PM
0
Thank you
With all credit to Tom Urtis from mrexcel

Depends on if the button is from the control toolbox or the Forms toolbar.

'If forms, this macro assigned to that button:
Sub test()
   MsgBox ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row
End Sub

'if CommandButton, this macro assigned to that button:
Private Sub CommandButton1_Click()
   MsgBox CommandButton1.TopLeftCell.Row
End Sub
0
Thank you
It is a form control from the developer ribbon that I inserted. I then assigned this macro:

Sub VernTest()
'
' VernTest Macro
' Select cells in current row
'

'
With ActiveCell
Range(Cells(.Row, "B"), Cells(.Row, "U")).Select
End With
End Sub

This macro selects the appropriate cells, but in whatever row that has the active cell.

I'm not sure how to include your command into this macro.

Also, I tried your solutions above, but couldn't get either to work.

I appreciate your help so fast! Thanks!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 24, 2011 at 01:33 PM
First you have to know what kind of button you have used.

The sample code that Tom had, was just a message box to tell you what row the button is

in your case you would have grab the row

some thing like this (assuming you have used a form button)

Sub VernTest()
'
' VernTest Macro
' Select cells in current row
   Dim lRow             As Long
   
   lRow = ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row
   Range(Cells(lRow, "B"), Cells(lRow, "U")).Select

End Sub
It worked! Awesome. Thanks a bunch!