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. |
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. |
All times are GMT +1. The time now is 11:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com