Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Cell Reference for Active Button
I have a worksheet that contains several rows that need to be hidden /
unhidden via a macro attached to a button (from the Form toolbar). I'd like to create one macro for all buttons. What I need to know is how to reference the cell (or row) that the selected button is located in. I can then hide the relevant rows below it. Heres my code so far. It currently require the user to select a cell on the row that the button is located. Sub Button2_Click() 'HIDE For i = 1 To 5 ActiveCell.Offset(i, 0).EntireRow.Hidden = True Next i End Sub Thanks Trevor |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Cell Reference for Active Button
Hi Trvor,
Try something like: '============= Public Sub Tester() Dim BTN As Button Set BTN = ActiveSheet.Buttons(Application.Caller) BTN.TopLeftCell.EntireRow.Hidden = True End Sub '<<============= --- Regards, Norman "Trevor Williams" wrote in message ... I have a worksheet that contains several rows that need to be hidden / unhidden via a macro attached to a button (from the Form toolbar). I'd like to create one macro for all buttons. What I need to know is how to reference the cell (or row) that the selected button is located in. I can then hide the relevant rows below it. Heres my code so far. It currently require the user to select a cell on the row that the button is located. Sub Button2_Click() 'HIDE For i = 1 To 5 ActiveCell.Offset(i, 0).EntireRow.Hidden = True Next i End Sub Thanks Trevor |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Cell Reference for Active Button
Hi Norman
Thanks, this is great. I also need to hide 5 rows below the current row but don't have an Offset option with the TopLeftCell function. Is there a way to hide the other rows? Trevor "Norman Jones" wrote: Hi Trvor, Try something like: '============= Public Sub Tester() Dim BTN As Button Set BTN = ActiveSheet.Buttons(Application.Caller) BTN.TopLeftCell.EntireRow.Hidden = True End Sub '<<============= --- Regards, Norman "Trevor Williams" wrote in message ... I have a worksheet that contains several rows that need to be hidden / unhidden via a macro attached to a button (from the Form toolbar). I'd like to create one macro for all buttons. What I need to know is how to reference the cell (or row) that the selected button is located in. I can then hide the relevant rows below it. Heres my code so far. It currently require the user to select a cell on the row that the button is located. Sub Button2_Click() 'HIDE For i = 1 To 5 ActiveCell.Offset(i, 0).EntireRow.Hidden = True Next i End Sub Thanks Trevor |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Cell Reference for Active Button
Hi Trevor,
'----------------- I also need to hide 5 rows below the current row but don't have an Offset option with the TopLeftCell function. Is there a way to hide the other rows? '----------------- Try: '============= Public Sub Tester() Dim BTN As Button Set BTN = ActiveSheet.Buttons(Application.Caller) BTN.TopLeftCell.Resize(5).EntireRow.Hidden = True End Sub '<<============= --- Regards, Norman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2002 - Cell Reference for Active Button
Brilliant - thanks again Norman.
"Norman Jones" wrote: Hi Trevor, '----------------- I also need to hide 5 rows below the current row but don't have an Offset option with the TopLeftCell function. Is there a way to hide the other rows? '----------------- Try: '============= Public Sub Tester() Dim BTN As Button Set BTN = ActiveSheet.Buttons(Application.Caller) BTN.TopLeftCell.Resize(5).EntireRow.Hidden = True End Sub '<<============= --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using the active cell as a reference in a function | Excel Worksheet Functions | |||
How to reference an active cell | New Users to Excel | |||
XL2002: Button Labels & Sheets... | Excel Programming | |||
Active row cell reference | Excel Worksheet Functions | |||
Active Cell Reference | Excel Worksheet Functions |