XL2002 - Cell Reference for Active Button

[Guest]

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

 

[Norman Jones]

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
'<<=============

 

[Guest]

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]

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
'<<=============

 

[Guest]

Brilliant - thanks again Norman.