SOLVED

Collapse and Expanding Worksheet Tabs

Copper Contributor

Dear Excel Community,

 

Is there a way that i can combine worksheets into expandable and collapse-able tab.

 

i.e. i have a total of 12 tabs one for each month and its too many tab so i would like to collapse the first three tab, january, february, and march into a tab called quarter 1, then i do the same for the next 3, april, may, and june into a second tab called quarter 2. and so on and so on.

 

Thank you very much for taking your time to help me with this issue

18 Replies

Hi,

 

Please check this link.

It may help you!

 

Hope that helps

Haytham

Dear Haytham,

 

Thank you for your response but the link below is not exactly what i'm looking for.

 

i want something kinda like this link here.

 

I'm not sure how to use that myself.

best response confirmed by Jamil Mohammad (Bronze Contributor)
Solution

Hello Nguyen,

 

The link you are referring to, is not going to help you, because that code there is expanding and collapsing all worksheets except two. The loop is Thisworkbook.sheets 

 

What you need is to have 12 Months worksheets and then 4 sheets each for a single quarter and then you need 4 separate codes to place in each of those quarter sheets.  In Addition, you need one helper worksheet that has to be there all the time and must not be deleted. that worksheet is needed, in case if you collapse all quarters and there has to be a sheet to be activated in order for VBA to avoid tossing error.

 

I have written the below code that loops through the array of worksheets for specific quarter like this 

ThisWorkbook.Sheets(Array("Jan", "Feb", "Mar"))

 

The animation shows how it works.  I have attached the workbook with code embedded to it.

 

 

 

Untitled-Project51010fd2245a5797.gif

 

 

 

Private Sub Worksheet_Activate()
' This is for first Quarter Sheet
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets
    Set sheetsArray = ThisWorkbook.Sheets(Array("Jan", "Feb", "Mar"))


    Application.ScreenUpdating = False
    If ShowHide1.Name = "Show Quarter1" Then

        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

        ShowHide1.Name = "Hide Quarter1"
        ShowHide1.Tab.Color = vbYellow

        Sheet1.Activate
    Else

        For Each sheet In sheetsArray
            If (sheet.Name <> ShowHide1.Name And sheet.Name <> AlwaysShow.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet

        ShowHide1.Name = "Show Quarter1"
        ShowHide1.Tab.Color = vbGreen

        AlwaysShow.Activate
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()
' This is for Second Quarter Sheet
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets
    Set sheetsArray = ThisWorkbook.Sheets(Array("Apr", "May", "Jun"))


    Application.ScreenUpdating = False
    If ShowHide2.Name = "Show Quarter2" Then

        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

        ShowHide2.Name = "Hide Quarter2"
         ShowHide2.Tab.Color = vbYellow

        Sheet6.Activate
    Else

        For Each sheet In sheetsArray
            If (sheet.Name <> ShowHide2.Name And sheet.Name <> AlwaysShow.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet

        ShowHide2.Name = "Show Quarter2"
         ShowHide2.Tab.Color = vbGreen

        AlwaysShow.Activate
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()

' This is for Third Quarter Sheet
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets
    Set sheetsArray = ThisWorkbook.Sheets(Array("Jul", "Aug", "Sep"))


    Application.ScreenUpdating = False
    If ShowHide3.Name = "Show Quarter3" Then

        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

        ShowHide3.Name = "Hide Quarter3"
         ShowHide3.Tab.Color = vbYellow

        Sheet10.Activate
    Else

        For Each sheet In sheetsArray
            If (sheet.Name <> ShowHide3.Name And sheet.Name <> AlwaysShow.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet

        ShowHide3.Name = "Show Quarter3"
        ShowHide3.Tab.Color = vbGreen

        AlwaysShow.Activate
    End If
    Application.ScreenUpdating = True
End Sub


Private Sub Worksheet_Activate()
' This is for forth Quarter Sheet
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets
    Set sheetsArray = ThisWorkbook.Sheets(Array("Oct", "Nov", "Dec"))


    Application.ScreenUpdating = False
    If ShowHide4.Name = "Show Quarter4" Then

        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

        ShowHide4.Name = "Hide Quarter4"
        ShowHide4.Tab.Color = vbYellow

        Sheet15.Activate
    Else

        For Each sheet In sheetsArray
            If (sheet.Name <> ShowHide4.Name And sheet.Name <> AlwaysShow.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet

        ShowHide4.Name = "Show Quarter4"
        ShowHide4.Tab.Color = vbGreen

        AlwaysShow.Activate
    End If
    Application.ScreenUpdating = True
End Sub

 

Private Sub Worksheet_Activate()
' This is for first Quarter Sheet
Dim sheet As Worksheet

Dim sheetsArray As Sheets
Set sheetsArray = ThisWorkbook.Sheets(Array("MONDAY 2.4.19", "TUESDAY 2.5.19", "WEDNESDAY 2.6.19", "THURSDAY 2.7.19", "FRIDAY 2.8.19", "SATURDAY 2.9.19"))


Application.ScreenUpdating = False
If ShowHide1.Name = "TOTALS 2.4 - 2.9.19" Then

For Each sheet In sheetsArray
sheet.Visible = xlSheetVisible
Next sheet

ShowHide1.Name = "TOTALS 2.4 - 2.9.19"
ShowHide1.Tab.Color = vbYellow

Sheet1.Activate
Else

For Each sheet In sheetsArray
If (sheet.Name <> ShowHide1.Name And sheet.Name <> AlwaysShow.Name) Then
sheet.Visible = xlSheetVeryHidden
End If
Next sheet

ShowHide1.Name = "TOTALS 2.4 - 2.9.19"
ShowHide1.Tab.Color = vbGreen

AlwaysShow.Activate
End If

 

I am trying to use the above code to collapse a weeks worth of sheets. It stops on If. showhide.name. 

Says Identifier under cursor is not defined. 

Hi Rusty,

 

please see attached workbook.  I have modified the workbook as per your need.

Thanks so much. that helped a ton. I figured out the names for my sheets in VB did not match. Thanks a ton. 

You are very welcome. Thanks for feedback

Hi Jamil -@Jamil Mohammad 

 

Thanks for the sample workbook. Can I ask as a complete novice, how to activate the code (i.e. how do I hide the sheets in your workbook)??

I'm not sure if I was expecting to see a button or option selector, but I can't figure out how to make it work!

Thank you

Hi Angela,

The code is activated automatically using worksheet even handler.

it is important to modify the sheet names in the code to make it work. If you post a sample workbook. I can modify the code and share the file with you.

Hi @Jamil Mohammad 

 

That would be very kind, thank you.

Attached is a sample workbook - I'd like the first 2 tabs to be visible at all times. 

The tab named 'Inv1' has 3 sub tabs which I'd like to be able to either expand or collapse.

Similarly with the tab named 'Inv2'.

 

Any help would be appreciated.

 

angela 

sorry @Jamil Mohammad  here is the file!

 

@Angela McGhin 

 

Please see attached.

@Jamil MohammadThat is fantastic, Jamil!!
Thank you so much. You've saved me hours of trying to figure it out!

Hi@Jamil Mohammad 

 

Thanks so much for the sample workbook you created.

I've used this a few times and have managed to get familiar with the code enough to be able to adapt it for other workbooks too.

 

However the one part I don't understand is this:

 

Else

For Each sheet In sheetsArray
If (sheet.Name <> Sheet6.Name And sheet.Name <> Sheet1.Name And sheet.Name <> Sheet10.Name) Then
sheet.Visible = xlSheetVeryHidden
End If
Next sheet

 

I've somehow managed to adapt it each time and it appears to be working, but can you explain in basic terms what this part of the code does so I can understand it better, in case I need to change it in future??

 

Thank you!!

 

Angela

@Angela McGhin 

 

You are welcome!  thanks for your feedback.

 

I understand that why it is confusing. I will try to explain.

 

Excel sheets have two alias  what is the commonly used worksheet name and the other is the "Code Name"    for more detail, i suggest you read this article https://www.spreadsheet1.com/vba-codenames.html

 

I used code names because if you rename the worksheet, the code will still work. 

 

The workbook only has VBA in two worksheet models which its code names are sheet3 and sheet6.  the codes are placed in the worksheet model because they are event driven (meaning the VBA will run if an a preset action is triggered) for example if you activate the workbook, it will expand and on next activation it will collapse. This is what means by event driven. 

 

So to understand the code fully. we have to look at each step of the code.

 

I am commenting it here

 

Private Sub Worksheet_Activate()
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets  ' declares sheetsarray as sheets which is sheets is an object
    
    'then assigns array of the three sheet names
    Set sheetsArray = ThisWorkbook.Sheets(Array("Inv2 TS", "Inv2 Exp", "Inv2 Sum"))
    

   
    Application.ScreenUpdating = False
    ' this one first check if the name is "show"
    If Sheet6.Name = "Show Inv2" Then

' if the abovementioned condition was true,  then below code will loop only in the sheets Inv2 TS", "Inv2 Exp", "Inv2 Sum
' and set them visible"unhide)
        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

' here it changes the name of then worksheet to Hide Inv2 and color to yellow
        Sheet6.Name = "Hide Inv2"
        Sheet6.Tab.Color = vbYellow

'activates the first sheet which is the invoice summary
        Sheet10.Activate
        
        ' now the above action will run if the sheet6 name was "Show Inv2"  but
        'if it is Not Show Inv2 then here the Else statement will run the below code
    Else

 ' this will loop through sheet array which are "Inv2 TS", "Inv2 Exp", "Inv2 Sum"
        For Each sheet In sheetsArray
        
        ' now this condition is checking if that it only should affect sheets that its name are not Sheet6 and Sheet1 and sheet10
        ' you can look there which sheets codes name are 6, 1, 10
        'then if the condition met, and exlcuded 6, 1, 10 then hide those sheets,
            If (sheet.Name <> Sheet6.Name And sheet.Name <> Sheet1.Name And sheet.Name <> Sheet10.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet
' then set a new name which is show Inv2 to the sheet6 name and also change color to green.
        Sheet6.Name = "Show Inv2"
        Sheet6.Tab.Color = vbGreen

        Sheet10.Activate
    End If
    Application.ScreenUpdating = True
End Sub

 

is important to mentioned here that the code can be simplified as below.

 

since we are running the code into an array of three sheets "Inv2 TS", "Inv2 Exp", "Inv2 Sum"

 

then it is unnecessary to have this condition

If (sheet.Name <> Sheet6.Name And sheet.Name <> Sheet1.Name And sheet.Name <> Sheet10.Name) Then
End If 

so in the code below, i removed the If (sheet.Name <> Sheet6.Name And sheet.Name <> Sheet1.Name And sheet.Name <> Sheet10.Name) Then   along with it closing End If

 

 

Private Sub Worksheet_Activate()
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets  ' declares sheetsarray as sheets which is sheets is an object
    
    'then assigns array of the three sheet names
    Set sheetsArray = ThisWorkbook.Sheets(Array("Inv2 TS", "Inv2 Exp", "Inv2 Sum"))
    

   
    Application.ScreenUpdating = False
    ' this one first check if the name is "show"
    If Sheet6.Name = "Show Inv2" Then

' if the abovementioned condition was true,  then below code will loop only in the sheets Inv2 TS", "Inv2 Exp", "Inv2 Sum
' and set them visible"unhide)
        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

' here it changes the name of then worksheet to Hide Inv2 and color to yellow
        Sheet6.Name = "Hide Inv2"
        Sheet6.Tab.Color = vbYellow

'activates the first sheet which is the invoice summary
        Sheet10.Activate
        
        ' now the above action will run if the sheet6 name was "Show Inv2"  but
        'if it is Not Show Inv2 then here the Else statement will run the below code
    Else
'code will loop only in the sheets Inv2 TS", "Inv2 Exp", "Inv2 Sum
' and set them visible"unhide)
        For Each sheet In sheetsArray
                  
               sheet.Visible = xlSheetVeryHidden
        Next sheet
' then set a new name which is show Inv2 to the sheet6 name and also change color to green.
        Sheet6.Name = "Show Inv2"
        Sheet6.Tab.Color = vbGreen

        Sheet10.Activate
    End If
    Application.ScreenUpdating = True
End Sub

@Jamil Mohammad 

 

Complete Excel novice....

I am trying to create a monthly spreadsheets with collapsible weeks.  I was hoping that I would be able to use one of the previous sheets you created and it doesn't work because I am not able to copy and rename.  Any help, guidance and direction would be great!

Hi

If you see the code, it is done for quarters, so it can be easily changed to weeks. you just need to pay attention to the SheetCode names. not a worksheet name but its codeName. Codename is different than sheetname. you can upload a dummy file, someone may help.
HI @Jamil Mohammad
I was trying to get this to work for an yearly distribution of sheets, but the error 424 keeps popping up. How do I fix it?
1 best response

Accepted Solutions
best response confirmed by Jamil Mohammad (Bronze Contributor)
Solution

Hello Nguyen,

 

The link you are referring to, is not going to help you, because that code there is expanding and collapsing all worksheets except two. The loop is Thisworkbook.sheets 

 

What you need is to have 12 Months worksheets and then 4 sheets each for a single quarter and then you need 4 separate codes to place in each of those quarter sheets.  In Addition, you need one helper worksheet that has to be there all the time and must not be deleted. that worksheet is needed, in case if you collapse all quarters and there has to be a sheet to be activated in order for VBA to avoid tossing error.

 

I have written the below code that loops through the array of worksheets for specific quarter like this 

ThisWorkbook.Sheets(Array("Jan", "Feb", "Mar"))

 

The animation shows how it works.  I have attached the workbook with code embedded to it.

 

 

 

Untitled-Project51010fd2245a5797.gif

 

 

 

Private Sub Worksheet_Activate()
' This is for first Quarter Sheet
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets
    Set sheetsArray = ThisWorkbook.Sheets(Array("Jan", "Feb", "Mar"))


    Application.ScreenUpdating = False
    If ShowHide1.Name = "Show Quarter1" Then

        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

        ShowHide1.Name = "Hide Quarter1"
        ShowHide1.Tab.Color = vbYellow

        Sheet1.Activate
    Else

        For Each sheet In sheetsArray
            If (sheet.Name <> ShowHide1.Name And sheet.Name <> AlwaysShow.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet

        ShowHide1.Name = "Show Quarter1"
        ShowHide1.Tab.Color = vbGreen

        AlwaysShow.Activate
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()
' This is for Second Quarter Sheet
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets
    Set sheetsArray = ThisWorkbook.Sheets(Array("Apr", "May", "Jun"))


    Application.ScreenUpdating = False
    If ShowHide2.Name = "Show Quarter2" Then

        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

        ShowHide2.Name = "Hide Quarter2"
         ShowHide2.Tab.Color = vbYellow

        Sheet6.Activate
    Else

        For Each sheet In sheetsArray
            If (sheet.Name <> ShowHide2.Name And sheet.Name <> AlwaysShow.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet

        ShowHide2.Name = "Show Quarter2"
         ShowHide2.Tab.Color = vbGreen

        AlwaysShow.Activate
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()

' This is for Third Quarter Sheet
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets
    Set sheetsArray = ThisWorkbook.Sheets(Array("Jul", "Aug", "Sep"))


    Application.ScreenUpdating = False
    If ShowHide3.Name = "Show Quarter3" Then

        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

        ShowHide3.Name = "Hide Quarter3"
         ShowHide3.Tab.Color = vbYellow

        Sheet10.Activate
    Else

        For Each sheet In sheetsArray
            If (sheet.Name <> ShowHide3.Name And sheet.Name <> AlwaysShow.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet

        ShowHide3.Name = "Show Quarter3"
        ShowHide3.Tab.Color = vbGreen

        AlwaysShow.Activate
    End If
    Application.ScreenUpdating = True
End Sub


Private Sub Worksheet_Activate()
' This is for forth Quarter Sheet
    Dim sheet As Worksheet

    Dim sheetsArray As Sheets
    Set sheetsArray = ThisWorkbook.Sheets(Array("Oct", "Nov", "Dec"))


    Application.ScreenUpdating = False
    If ShowHide4.Name = "Show Quarter4" Then

        For Each sheet In sheetsArray
            sheet.Visible = xlSheetVisible
        Next sheet

        ShowHide4.Name = "Hide Quarter4"
        ShowHide4.Tab.Color = vbYellow

        Sheet15.Activate
    Else

        For Each sheet In sheetsArray
            If (sheet.Name <> ShowHide4.Name And sheet.Name <> AlwaysShow.Name) Then
               sheet.Visible = xlSheetVeryHidden
            End If
        Next sheet

        ShowHide4.Name = "Show Quarter4"
        ShowHide4.Tab.Color = vbGreen

        AlwaysShow.Activate
    End If
    Application.ScreenUpdating = True
End Sub

 

View solution in original post