Hide Rows By reference to a cell

[SuitedAces]

Please offer me some help and education with this;

I have a named cell on a sheet.
There are a large number of buttons that will each toggle a row
hidden/unhidden.
The 1st button will hide/unhide a row 3 rows below the named cell.
The 2nd button will hide/unhide a row 6 rows below the named cell.
and so on..... the 3rd button 9 rows below.

I would like to call one sub and use a variable.
And I would like to have the code determine the row in reference to
the
named cell.
So that if rows are ever inserted above the range the rows that are
toggled hidden/unhidden will maintain that distance from the named
cell.

Thank you for your help.

 

[Ken Johnson]

Please offer me some help and education with this;

I have a named cell on a sheet.
There are a large number of buttons that will each toggle a row
hidden/unhidden.
The 1st button will hide/unhide a row 3 rows below the named cell.
The 2nd button will hide/unhide a row 6 rows below the named cell.
and so on..... the 3rd button 9 rows below.

I would like to call one sub and use a variable.
And I would like to have the code determine the row in reference to
the
named cell.
So that if rows are ever inserted above the range the rows that are
toggled hidden/unhidden will maintain that distance from the named
cell.

Hi SuitedAces,

I used 3 buttons from the Forms toolbar (I avoid the buttons from the
Control toolbar for compatability with Macs)

I made their captions Hide Row 3, Hide Row 6 and Hide Row 9.

I assigned each one of them to the following macro...

Public Sub Hide_Row_N()
Dim Pressed As Shape
Set Pressed = ActiveSheet.Shapes(Application.Caller)
Select Case Pressed.TextFrame.Characters.Text

Case "Hide Row 3"
Range("MyCell").Offset(3, 0).EntireRow.Hidden = _
Not Range("MyCell").Offset(3, 0).EntireRow.Hidden

Case "Hide Row 6"
Range("MyCell").Offset(6, 0).EntireRow.Hidden = _
Not Range("MyCell").Offset(6, 0).EntireRow.Hidden

Case "Hide Row 9"
Range("MyCell").Offset(9, 0).EntireRow.Hidden = _
Not Range("MyCell").Offset(9, 0).EntireRow.Hidden

End Select
End Sub

I also named one of the cells on the sheet "MyCell"

I think this does what you're requesting.

It is easily extended to the number of buttons you are using by simply
adding additional Case lines.

For example, a button with the caption Hide Row 12 would need the
following lines added to the Select Case...

Case "Hide Row 12"
Range("MyCell").Offset(12, 0).EntireRow.Hidden = _
Not Range("MyCell").Offset(12, 0).EntireRow.Hidden

Hope this helps you.

Ken Johnson

 

[SuitedAces]

Thank you

The code functions perfectly.

This makes clear to me how I reference the row and how to toggle the
row hidden/unhidden.

But I have this question - with the buttons from forms does this force
me to have a caption on the button

 

[Ken Johnson]

But I have this question - with the buttons from forms does this force
me to have a caption on the button ?

Hi SuitedAces,

Yes, the code uses the caption to determine which row to hide/unhide.
However, you can format the button so that the Font color nearly
matches the background color and you could set the Font size to 1 pt so
that it is barely visible.

Ken Johnson

 

[Ken Johnson]

Hi SuitedAces,

I've come up with a way of having captionless buttons.

The code now relies on the buttons' names.

You will have to, however do the following with each individual
button...

1. Select one
2. Click inside the Name Box on the left side of the Formula bar
3. Type "Toggle 3" if the selected button is the one for toggling the
third row. The code as it stands relies on this being correct. If you
want to use a different name you will have to alter the code.
4. Press Enter. Be careful here, I often forget to press Enter then
the name is not changed.

Make sure you change all the button's names for the code to use ie
Toggle 6, Toggle 9

The code for this technique is ...

Public Sub Hide_Row_N()
Dim Pressed As Shape
Set Pressed = ActiveSheet.Shapes(Application.Caller)
Select Case Pressed.Name

Case "Toggle 3"
Range("MyCell").Offset(3, 0).EntireRow.Hidden = _
Not Range("MyCell").Offset(3, 0).EntireRow.Hidden

Case "Toggle 6"
Range("MyCell").Offset(6, 0).EntireRow.Hidden = _
Not Range("MyCell").Offset(6, 0).EntireRow.Hidden

Case "Toggle 9"
Range("MyCell").Offset(9, 0).EntireRow.Hidden = _
Not Range("MyCell").Offset(9, 0).EntireRow.Hidden

End Select
End Sub

Hope this makes sense

Ken Johnson

 

[Ken Johnson]

Hi SuitedAces,

When you type the name in the name box on the left of the formula bar
don't include the speech marks. You probably know that, I'm just making
sure.

Ken Johnson

 

[SuitedAces]

Ken

Thats great for your help

 

[Ken Johnson]

Hi SuitedAces,

You're welcome, thanks for the feedback.

Ken Johnson