Thread: Row Command button location

In the example below the button in Col A represents a command button that has been created via the control toolbox. I would like to select the button and know what cell (row) the button is in. The button should become the active cell.

How can you reference the row to identify the location of a command button? I have the code to insert the row, but need to tie it to the button w/o hard coding Find "Blue", etc. for each button.

IE:

COL A COLB
Button Blue
Blue

Button Green


Button Yellow
Yellow

Thanks in advance!

CommandButton1.TopLeftCell.Row

or

CommandButton1.BottomRightCell.Row

will return the row of the command button.

HTH

Jason

That was too simple. Thank you. I spent 1/2 the day today trying to figure that one out.

Hi

Buttons aren't connected to a row, so it becomes difficult to try to associate a row with a button. You could code each button with some details, but that would get tedious.

One way I've seen to get around this, is to not use buttons, but to use event macros. If you use the sheet beforedoubleclick event, you can pretty easily get the row.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column = 1 And Target.Cells.Count = 1 Then
    MsgBox ActiveCell.Row
  End If
End Sub

hth

rylo