Show/Hide Sheets

[dyurkovic]

Hi All,
This forum has been so helpful as I am trying to learn about VBA and macros. I do have a question. I have a workbook with a ton of worksheets. The main sheet is "2019". Then each month has 3 different sheets: "January Late Cancel", "January No Show", "January Early Departures", then "February Late Cancel", etc.

On sheet "2019" I currently have a button for each month that will toggle between hiding and unhiding the month's sheets, while keeping "2019" open as well. I also have a button for 2019 which will show/hide all of the sheets in the workbook (with the exception of "2019" of course).

Here is my problem: If, for example, the sheets for January are already visible, when I click on the 2019 button, all sheets are visible. This is good. But, if I click on the 2019 button again, all of the sheets EXCEPT the January ones will hide. This does not happen if all of the sheets are hidden to begin with and I click the 2019 button to unhide and then click again to hide. It will hide all sheets.

Can someone help? If i click on the 2019 button to hide all sheets, I want it to hide all sheets, regardless of if they were visible before. Below is the code I am using for the 2019 button:

Sub ShowHide2019()
Dim Cell As Range
For Each Cell In Range("P30:P65")
ActiveWorkbook.Worksheets(Cell.Value).Visible = Not ActiveWorkbook.Worksheets(Cell.Value).Visible
Next Cell
End Sub


Any help is greatly appreciated!
~Deanna

 

[My Aswer Is This]

Try this:

Sub ShowHide2019()
Dim Cell As Range
For Each Cell In Range("P30:P65")
ActiveWorkbook.Worksheets(Cell.Value).Visible = False
Next Cell
End Sub

 

[dyurkovic]

Thank you so much for the quick reply. This did successfully hide all sheets, but it doesn't have the toggle option to also open all sheets. I'm hoping to have one button both hide and unhide all of the worksheets.

~Deanna

 

[My Aswer Is This]

Your question was:

Can someone help? If i click on the 2019 button to ​

hide all sheets​

, I want it to hide all sheets, regardless of if they were visible before. Below is the code I am using for the 2019 button:

That what my script does it hides all sheets.
​

 

[My Aswer Is This]

The only way to do what I believe your now wanting is to have a Input box popup and ask if you want to hide or unhide all sheets

I know of no way to toggle all sheets hidden or unhidden if some are hidden and some are visible.

 

[dyurkovic]

I apologize if my original question was unclear. I am hoping to have a button that will toggle between hiding and unhiding a specified range of worksheets in my workbook, regardless of whether or not there are worksheets already visible/hidden. When I click the button to hide, I want all of the worksheets in the specified range to be hidden. When I click the same button to unhide, I want all of the worksheets in the specified range to be unhidden. My above code does this, if there are no worksheets visible to begin with. If there are some worksheets already visible, the button will unhide the rest on the first click, but when I click it again to hide the worksheets, only the worksheets that were hidden to begin with will hide again.

I hope this is more clear. Thank you for your time!

~Deanna

 

[dyurkovic]

The only way to do what I believe your now wanting is to have a Input box popup and ask if you want to hide or unhide all sheets

I know of no way to toggle all sheets hidden or unhidden if some are hidden and some are visible.

I was afraid that would be the answer. Thank you very much for your time.

 

[My Aswer Is This]

I'm just curios thinking of ways to do this:

Are all the sheets in this workbook for 2019
Or do you also have sheets in this workbook for 2018 and 2017 etc.

If so how are you sheets named

Are they like 2019 January....

So is the Year and Month Name a part of the sheet name

Because you said I have tons of sheets.

 

[dyurkovic]

Currently I only have sheets: “2019” and then 3 sheets for each month. So 37 sheets total. Potentially going to add future years as well, though that hasn’t been decided yet. After reading more threads in this forum, I’m beginning to realize that my “tons” of sheets is probably pretty insignificant compared to if of the other projects.

 

[My Aswer Is This]

So what happens next year 2020

You will Have a sheet named 2020
And 36 sheets named January... February... etc.

This will now result in Duplicate sheet names.
Which Excel will not allow.

Maybe you might want to name your sheets:

2020 January...
2020 February...

Here is a easy way to make all new sheets for 2019

Test this is a Copy of your current Workbook.

I always test scripts in a copy of my Workbook just in case

You would need to run this script in the Month of December
But I could modify that if needed

Sub Add_Sheets()
'Modified 12/9/2018 1:20 PM  EST
Application.ScreenUpdating = False
Dim i As Long
For i = 1 To 12
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = Format(DateAdd("M", i, Date), "YYYY MMMM") & " Late Cancel"
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = Format(DateAdd("M", i, Date), "YYYY MMMM") & " No Show"
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = Format(DateAdd("M", i, Date), "YYYY MMMM") & " Early Departures"
Next
Application.ScreenUpdating = True
End Sub