Form Control Button Offset

[KIM W]

How can I refer to an offset from a Form Control Button?
The cell to which the button is attached may move (and change row number
address, obviously) due to row sorting, but I always want the macro used by
the button to select the cell one column to the right of the button and down
to the first blank cell.
There will be multiple buttons on the worksheet that need this behavior--
over one column, down to first empty cell.
I am designer of the worksheet, so I have flexibility on solutions and
alternative approaches.
Thanks in advance.

 

[Doug Glancy]

Kim,

I think multiple buttons moving around is going to be hard to manage.
Instead one approach is try to capture the double-click event in the
appropriate cells in that column, like:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Dim lngFirstBlankRow As Long
With Me
If Not Intersect(Target, .Range("B:B")) Is Nothing Then ' adjust for the
appropriate column
lngFirstBlankRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
If Target.Row < lngFirstBlankRow Then
Cancel = True 'cancel the default double-click action
.Range("B" & Target.Row & ":B" & lngFirstBlankRow).Select
End If
End If
End With
End Sub

This code goes in the code module for the worksheet in question.

More understandabel to users would be to create a menu, maybe a right-click
menu, to offer the choice when they are in an appropriate cell. See this
page for 2007 and 2003- menus:

http://spreadsheetpage.com/index.php/tip/C30

hth,

Doug