Mar 12 2018 04:54 PM
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
Mar 13 2018 05:52 AM
Mar 13 2018 09:54 AM
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.
Mar 13 2018 11:40 AM
SolutionHello 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.
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
Feb 14 2019 08:28 AM
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.
Feb 14 2019 09:42 AM
Hi Rusty,
please see attached workbook. I have modified the workbook as per your need.
Feb 14 2019 10:00 AM
Thanks so much. that helped a ton. I figured out the names for my sheets in VB did not match. Thanks a ton.
Feb 19 2019 11:29 AM
Apr 09 2019 03:33 AM
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
Apr 09 2019 05:48 AM
Apr 12 2019 07:28 AM
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
Apr 12 2019 07:29 AM
sorry @Jamil Mohammad here is the file!
Apr 12 2019 09:07 AM
Apr 15 2019 12:51 AM
@Jamil MohammadThat is fantastic, Jamil!!
Thank you so much. You've saved me hours of trying to figure it out!
Jun 25 2019 05:34 AM
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
Jun 25 2019 06:32 AM
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
Feb 21 2020 08:35 AM
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!
Feb 24 2020 05:31 AM
Dec 12 2021 05:52 PM
Mar 13 2018 11:40 AM
SolutionHello 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.
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