Hide/unhide rows with buttons

[tracktor]

I am using several toggle buttons to hide/show rows of data related to the specific toggle buttons. I also want to hide and show the toggle buttons as well. When I try to do that the data underneath the toggle buttons is hidden but the toggle buttons don't hide with the cells.

A solution for the problem that I am having would be to have the ability to click on a cell as if it were a toggle button and hide/show rows of data when clicked.

OR

If there is a way to hide/show buttons as well would work too.

Your help is greatly appreciated.

 

[ZVI]

The toggle buttons move and size with cells but they don't hide with cells. Once again... I can't use controls when hiding rows.

If Toggle Button with settings of post#8 is over the row(s) which to be switches hidden/unhidden then such Toggle Button will become hidden/unhidden with that row(s) as well.

There are no any Toggle Buttons manipulations in your code and its functionality for me is different to your description.

So, please describe your task more detailed:
What are you trying to do (goal, scenario)?
How many toggle buttons are used?
Are your toggle buttons from Visual Basic control panel or from Form control panel?
Where are the positions of your toggle buttons relative to the hidden/unhidden rows?
What do you expect if you hit ToggleButton1, ToggleButton2 and others?
What do you expect if manually hide/unhide some (what address) rows?

 

[tracktor]

I have several hundreds of rows of data on one worksheet. There are dozens of headings and dozens of sub headings. I want to hide all of the rows with data under every heading and sub heading. And hide all sub headings under their respective headings.

The problem that I am having with controls is that when you hide rows with controls in any of the cells of the hidden rows the rows hide but the controls don't.

So I would like to be able to click on a cell once and it hides specific rows. Then when it is clicked again the rows are unhidden.

Right now I can select a cell to hide rows or show rows but not both. And another problem with this is... if the cell is tabbed into it shows or hides the rows.

So... The cell actually has to show rows when they are hidden, and hide rows when they are showing. And the cell has to be clicked on, not tabbed into.

I hope this explains what I am looking for. Sorry for the confusion.

 

[ZVI]

Thank you - it's clear description.

But because nothing was said about buttons let me inform you once more that if you need the controls which will be hidden/unhidden with its placement rows/columns, then use controls from Visual Basic control panel rather than controls from the Form control panel.

This is the same instruction as in post#8 but more detailed:
1. Activate Visual Basic control panel by the aid of menu View – Control Panels – Visual Basic
2. Drag & drop control from the panel to the sheet
3. Check if design mode is switched on (find such button on Visual Basic panel)
4. Select dropped control if it’s not selected
5. Do right click on control and choose: Format Control option
6. Choose Properties tab
7. Choose Move and size with cells option
8. Press Ok button

After that the control placed over the rows became hidden/unhidden if you switches these rows to hidden/unhidden.

To hide/unhide of grouped rows typically the menu Data - Group and Outline is used.
You can find more details in online help article Outline a list of data in a worksheet.

As for the using of worksheet events to hide/unhide of some rows I would recommend Before Double Clicking event instead of Selection Change one.

Here is an example of the code:

' Code of worksheet module
' Try double clicking on cells: A1, A24, A31
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  
  ' Variable for Hidden state of the row below the Target
  Dim IsHidden As Boolean
    
  ' Only column A is reserved for d/clicking now
  If Target.Column <> 1 Then Exit Sub
  
  ' Set IsHidden
  IsHidden = Target.Offset(1).EntireRow.Hidden
  
  ' Hide/Unhide some rows below Target, depending of Target (heading-subheading) row
  Select Case Target.Row
    ' Apply your Case settings and its dependent hidden/unhidden rows
    Case 1  ' row 1, cell A1
      Rows("2:5").Hidden = Not IsHidden
      Cancel = True
    Case 24 ' row 24, cell A24
      Rows("25:30").Hidden = Not IsHidden
      Cancel = True
    Case 31 ' row 31, cell A31
      Rows("32:40").Hidden = Not IsHidden
      Cancel = True
  End Select
  
End Sub

Regards,
Vladimir

 

[tracktor]

Vladimir,

That works perfectly!

Thank you very much!

 

[ZVI]

Vladimir,

That works perfectly!

Thank you very much!

Glad that has helped,
Cheers
Vladimir

 

[nickthap]

Hi,

I am using a toggle button to do this (hide/undhide rows by clicking a togglebutton), and this is the code in VBA that I am using:

Private Sub ToggleButton5_Click()

If ToggleButton5.Value = True Then
'This area contains the things you want to happen
'when the toggle button is not depressed
Rows(34).EntireRow.Hidden = False
Rows(35).EntireRow.Hidden = False
Rows(36).EntireRow.Hidden = False
Rows(37).EntireRow.Hidden = False
Rows(38).EntireRow.Hidden = False
Rows(39).EntireRow.Hidden = False
Rows(40).EntireRow.Hidden = False
Rows(41).EntireRow.Hidden = False
Rows(42).EntireRow.Hidden = False
Rows(43).EntireRow.Hidden = False
Rows(44).EntireRow.Hidden = False
Rows(45).EntireRow.Hidden = False
Rows(46).EntireRow.Hidden = False
Rows(47).EntireRow.Hidden = False
Rows(48).EntireRow.Hidden = False

Else
'This area contains the things you want to happen
'when the toggle button is depressed
Rows(34).EntireRow.Hidden = True
Rows(35).EntireRow.Hidden = True
Rows(36).EntireRow.Hidden = True
Rows(37).EntireRow.Hidden = True
Rows(38).EntireRow.Hidden = True
Rows(39).EntireRow.Hidden = True
Rows(40).EntireRow.Hidden = True
Rows(41).EntireRow.Hidden = True
Rows(42).EntireRow.Hidden = True
Rows(43).EntireRow.Hidden = True
Rows(44).EntireRow.Hidden = True
Rows(45).EntireRow.Hidden = True
Rows(46).EntireRow.Hidden = True
Rows(47).EntireRow.Hidden = True
Rows(48).EntireRow.Hidden = True
End If

End Sub
​

My question is, can I express this as a range (rows 34-48) instead of having to list out each row to hide/unhide? Setting this doc up would be so much faster...

 

[tracktor]

My question is, can I express this as a range (rows 34-48) instead of having to list out each row to hide/unhide? Setting this doc up would be so much faster...

I beleive this might work.

Rows("2:35").Hidden

or this

Range("A51:A52").EntireRow.Hidden

The below code works without toggles and works on protected sheet. Paste this in an empty worksheet and see if this works for you. The password to unprotect the sheet is "abc". The sheet will be protected as soon as you activate the code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  ' Variable for Hidden state of the row below the Target
  Dim IsHidden As Boolean
 
  ' Only column A is reserved for d/clicking now
  If Target.Column <> 1 Then Exit Sub
  ' Set IsHidden
  IsHidden = Target.Offset(1).EntireRow.Hidden
  ActiveSheet.Unprotect Password:="abc"
  ' Hide/Unhide some rows below Target, depending of Target (heading-subheading) row
  Select Case Target.Row
 
    ' Apply your Case settings and its dependent hidden/unhidden rows
    Case 1  ' row 1, cell A1
      Rows("2:35").Hidden = Not IsHidden
      Cancel = True
 
    Case 24 ' row 24, cell A24
      Rows("25:30").Hidden = Not IsHidden
      Cancel = True
    Case 31 ' row 31, cell A31
      Rows("32:40").Hidden = Not IsHidden
      Cancel = True
 
    End Select
   ActiveSheet.Protect Password:="abc"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="abc"
Range("A51:A52").EntireRow.Hidden = (Range("A50").Value = "c")
Range("A53").EntireRow.Hidden = (Range("B50").Value = "c")
ActiveSheet.Protect Password:="abc"
End Sub

The cells you need to double click in are A1, A24, and A31

and in cell A50 and B50 type a "c" to hide and "g" to show, change the font on these cells to Marlett

This was the best solution for me.

Hope you like it.

 

[ZVI]

Hi,

I am using a toggle button to do this (hide/undhide rows by clicking a togglebutton), and this is the code in VBA that I am using:

Private Sub ToggleButton5_Click()
    If ToggleButton5.Value = True Then
        Rows(34).EntireRow.Hidden = False
         ...
        Rows(48).EntireRow.Hidden = False
    Else
        Rows(34).EntireRow.Hidden = True
        ...
        Rows(48).EntireRow.Hidden = True
    End If
End Sub

My question is, can I express this as a range (rows 34-48) instead of having to list out each row to hide/unhide? Setting this doc up would be so much faster...

Hi,
Try this:

Private Sub ToggleButton5_Click()
  Rows("34:48").Hidden = Not ToggleButton5.Value
End Sub

 

[johnlucasprice]

I can easily get 2 buttons, 1 for show, 1 for hide. Any way to make the same button hide when you click, then show when you hide again? When I go to assign a macro, I can only assign one macro at a time, either my "hide" macro or my "show" marco. Any suggestions?

 

[mikerickson]

If you want to control it with one cell, something like this should work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        If Target.Address = "$A$23" Then
            
            Application.EnableEvents = False
            Rows(24).Hidden = (Range("A23") = "Hide Row 24")
            Range("25:30").EntireRow.Hidden = Not (Range("A23") = "Hide Row 24")
            Range("A23").Value = "Hide Row" & IIf(Rows(24).Hidden, "s 25-30", " 24")
            Range("A24:A25").SpecialCells(xlCellTypeVisible).Select
            Application.EnableEvents = True
        End If
    End If
End Sub

If that one cell is in A24:A30, it won't work. (How does one click on a hidden cell to unhide that cell?)