VBA for Expanding and Collapsing Sheets/Tabs

[Bkisley]

Hello everyone, I have a file that I use for quoting. For each product I have the following sheets/tabs....Cost, Financial Model, NPV, Sensitivity, and Amort. When I have multiple products I am quoting at the same time I end up having one file with a very long list of sheets.

I want to be able to have one sheet/tab called "Show Product 1" and when I click that it shows all 5 sheets/tabs mentioned above and changes the name to "Hide Product 1". When I click on "Hide Product 1" I want to hide all 5 of those sheets and change the name back to "Show Product 1"

I found this VBA online but I am having difficulty with it - exact part is show below in red.

Private Sub Worksheet_Activate()
Dim sheet As Worksheet
Application.ScreenUpdating = False
If ShowHide.Name = "Show Product 1" Then
'Make all sheets visible
For Each sheet In ThisWorkbook.Sheets
sheet.Visible = xlSheetVisible
Next sheet
'Change the sheet name to the "Collapse" name you want
ShowHide.Name = "Hide Product 1"
'Pick a sheet to display after the once hidden sheets are expanded
Sheet4.Activate
Else
'Hide all sheets except the ones you want to keep visible
For Each sheet In ThisWorkbook.Sheets
If (sheet.Name <> Results.Name And sheet.Name <> Run.Name And sheet.Name <> ShowHide.Name) Then
sheet.Visible = xlSheetVeryHidden
End If
Next sheet
'Change the sheet name to the "Expand" name you want
ShowHide.Name = "Show Product 1"
'Pick a sheet to display after the sheets to be hidden are collapsed
Run.Activate
End If
Application.ScreenUpdating = True
End Sub


PLEASE HELP!

 

[footoo]

What difficulty are you having?
Is there an error message box?
Check that you actually have sheets with codenames "Results" and"Run"

 

[Bkisley]

What difficulty are you having?
Is there an error message box?
Check that you actually have sheets with codenames "Results" and"Run"

I can make it so when I click "Hide Product 1" it hides the correct sheets, however when I then click "Show Product 1" after that I get "Run-time error '424': Object Required"


The excel template where I got the code from has the following sheet names...Mapping, Calcs 1, Calcs 2, Filter, Calcs 3. It does not have sheets called "Results" or "Run" unless you mean something else?

 

[footoo]

So what are "Results", "Run" and "ShowHide"?

The line Run.Activate activates the sheet that has the CodeName "Run".

Try putting at the start of your code : Application.EnableEvents=False
And at the end of the code : Application.EnableEvents=True

After this change, if the code is interrupted and does not complete, you will need to run Application.EnableEvents=True (or close and re-open Excel).

 

[Bkisley]

I did not make this code - I googled what I am looking for and copy and pasted it into my document. So I have no idea what any of that means. Are you able to change the code I have above to what you think it should be?

 

[footoo]

I've just tested your code and it works.

As mentioned in post #2, you need to check the sheet CodeNames.

Press Alt + F11 to go to the VB Editor
In the Project window on the left, expand the workbook's VBA Project
Expand Microsoft Excel Objects

Listed under Microsoft Objects there will be a list of the sheets in the format :
Sheet CodeName (Sheet Tab Name)

There should be one that reads either:
ShowHide(Show Product 1)
or:
ShowHide(Hide Product 1)

Also there must be two other sheets with CodeNames "Results" and "Run".
If either of these CodeNames do not exist, then the code will fail on the following line:
If (sheet.Name <> Results.Name And sheet.Name <> Run.Name And sheet.Name <> ShowHide.Name) Then

From what you say in your original post, I suspect the CodeName "Results" is missing.

 

[footoo]

From what you say in your original post, I suspect the CodeName "Results" is missing.

If so, for the sheet that needs the CodeName “Results” :
- In the Project window select the sheet
- Go to View/Properties Window
- In the Properties window in the (Name) box, change the name to Results and press Enter
- Check that this revised CodeName is correctly shown in the Project window