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

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

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

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

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