Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Control Button Offset
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form Control Button Offset
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 "KIM W" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form Control Button Color | Excel Discussion (Misc queries) | |||
Button in Form Control | New Users to Excel | |||
How to see which Marco is assgined to a form control button | Excel Discussion (Misc queries) | |||
Changing Cell Link on Spin Button Form Control | Excel Discussion (Misc queries) | |||
Customizing Form Control Button in 2007 | Excel Discussion (Misc queries) |