ToggleButton HideUnhide rows across multiple sheets

duranimal86

New Member
Joined
Jul 24, 2019
Messages
18
Office Version
  1. 365
Platform
  1. Windows
I have a workbook where 6 of the sheets are the same (a Summary sheet and 5 input sheets) and i want to be able to toggle showing/hiding two groups of rows across all 6 sheets. The two groups of rows (AM & PM) that I want to toggle between displaying/hiding (ex. If AM shown, then PM hidden and vice versa). I have added an ActiveX ToggleButton and got it to work for the Summary sheet, but can't get it to also work across the 5 input sheets (sheets are identical, so same rows to show/hide on all sheets). Note: There are other sheets in the workbook, so I need to specify sheets instead of just looping through all sheets.

Also, I am far from a VBA expert and can only piece together code from online, so any improvement suggestions are more than welcome. Just be warned that i might ask for an explanation to help me learn.

VBA Code:
Private Sub ToggleButton1_Click()

Dim ws As Worksheet
Dim sheetList As Sheets


Set AM = Range("7:25")
Set PM = Range("26:44")


If AM.EntireRow.Hidden = True Then
    AM.EntireRow.Hidden = False
    PM.EntireRow.Hidden = True
    ToggleButton1.Caption = "AM Meeting"
    ToggleButton1.BackColor = vbYellow

Else

AM.EntireRow.Hidden = True
PM.EntireRow.Hidden = False
ToggleButton1.Caption = "PM Meeting"
ToggleButton1.BackColor = vbBlue

End If


End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
This was a lot harder than I thought it should be. I could not, for the life of me, get the rows into a range variable that would work in a loop, so I changed the rows to a string variable and fed that into the row function inside the loop. I also couldn't get the ActiveX toggle to look right between toggles without selecting another cell, so I reselected the cell you were in at the beginning. It's not as elegant as I'd like (you change color and caption on the same button with every change of worksheet but it saved another If statement) but it should work fast enough for less than a hundred sheets or so. lol
For the array, the sheets are listed in order under the Excel Objects, just use the number, not what's in parenthesis.

VBA Code:
Private Sub ToggleButton1_Click()
Dim sheetList, AM, PM, getloc
AM = "7:25"
PM = "26:44"
getloc = Application.ActiveCell.Address 'get the original cursor location

sheetList = Array(1, 3, 6) ' add only the worksheet numbers you want to hide/show

'loop through all the sheets you have in the array
For i = 0 To UBound(sheetList)
    If ToggleButton1 = True Then 'check the button state rather than visiblity
        With Sheets(sheetList(i))
            .Rows(AM).Hidden = False
            .Rows(PM).Hidden = True
        End With
        ToggleButton1.Caption = "AM Meeting"
        ToggleButton1.BackColor = vbYellow
        ToggleButton1.ForeColor = vbBlack
    Else
        With Sheets(sheetList(i))
            .Rows(AM).Hidden = True
            .Rows(PM).EntireRow.Hidden = False
        End With
        ToggleButton1.Caption = "PM Meeting"
        ToggleButton1.BackColor = vbBlue
        ToggleButton1.ForeColor = vbWhite
    End If
Next
Range(getloc).Select 'select the cell you started from to repaint the button
End Sub
 
Upvote 0
Solution
Thanks for the quick reply. That worked great for all the other sheets, but now for some reason it isn't working on Sheet1 (which is the sheet with the button). Any ideas?

VBA Code:
Private Sub ToggleButton1_Click()

Dim sheetList, AM, PM, getloc
AM = "7:25"
PM = "26:44"
getloc = Application.ActiveCell.Address 'get the original cursor location

sheetList = Array(1, 3, 4, 5, 6, 7) ' add only the worksheet numbers you want to hide/show

'loop through all the sheets you have in the array
For i = 0 To UBound(sheetList)
    If ToggleButton1 = True Then 'check the button state rather than visiblity
        With Sheets(sheetList(i))
            .Rows(AM).Hidden = False
            .Rows(PM).Hidden = True
        End With
        ToggleButton1.Caption = "AM Meeting"
        ToggleButton1.BackColor = vbYellow
        ToggleButton1.ForeColor = vbBlack
    Else
        With Sheets(sheetList(i))
            .Rows(AM).Hidden = True
            .Rows(PM).EntireRow.Hidden = False
        End With
        ToggleButton1.Caption = "PM Meeting"
        ToggleButton1.BackColor = vbBlue
        ToggleButton1.ForeColor = vbWhite
    End If
Next
Range(getloc).Select 'select the cell you started from to repaint the button
End Sub
 
Upvote 0
Ok, after some troubleshooting i figured out what is happening but i don't understand it. Sheet2 is a background sheet created by an add-in and was xlSheetVeryHidden. After unhiding it i saw that it was getting the rows hidden for #1 in the Array. This was confirmed when i replaced 1 with 2 in the Array and it started working on the Summary (Sheet1) as expected. So do you have any idea why 1 & 2 are mixed up in the Array vs the Sheet#?

1659108681862.png
 
Upvote 0
So your array is 2, 3, 4, 5, 6, 7 and it works correctly? No clue on that! Glad you got it working, though.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top