Collapse and Expanding Worksheet Tabs

Jamil 

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!

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

HiJamil 

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

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