ToggleButton HideUnhide rows across multiple sheets

[duranimal86]

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.

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

 

[portews]

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.

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

 

[duranimal86]

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?

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

 

[duranimal86]

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#?

 

[portews]

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