VBA to hide sheets based off cell range and sheet names in another range

[muhleebbin]

Is there a better way to write the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("E1") = "Yes" Then
    Sheets("January").Visible = False
    Else
    Sheets("January").Visible = True
End If

If Range("E2") = "Yes" Then
    Sheets("February").Visible = False
    Else
    Sheets("February").Visible = True
End If

'continues on for the remaining months

I have a 13th sheet (sheet1) where it contains the calculations on if a sheet should be hidden. Range E1:E12 calculates whether the sheet should be hidden or not and Range A1:A12 has the sheet names January-December. Also is this code better fit for Private Sub Workbook_Open()? No one will actually change anything or type anything in Sheet1 so that's why I think this might be a better fit for Workbook_Open. Basically trying to create a workbook that has monthly data that will automatically hide each prior month as the year progresses and opens to the current months sheet.

Thanks in advance for your assistance

 

[My Aswer Is This]

So, your, wanting a macro that would run when you open your workbook.
All sheets would be hidden except for the one named todays Month.

Like for example today 10/27/2022 if the workbook was opened the only sheet that would be visible would be one named "October" is that what your wanting?

 

[muhleebbin]

It would also keep November and December visible. Cell H1 is a today function and L1 is number of days after a month before that prior month gets hidden. So if the date was 10/10/2022 it would keep September visible but on the 11th it would hide September. With that in mind, based off the day, I do want the workbook to open to the current month (October in this case). I believe the following code works for that:

Sheets(Format(Now,"mmm")).Activate

Below is the worksheet snip:

Hide Sheet by Month.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	January	1	1/31/2022	31	Yes			10/27/2022	2022	10		10
2	February	2	2/28/2022	28	Yes
3	March	3	3/31/2022	31	Yes
4	April	4	4/30/2022	30	Yes
5	May	5	5/31/2022	31	Yes
6	June	6	6/30/2022	30	Yes
7	July	7	7/31/2022	31	Yes
8	August	8	8/31/2022	31	Yes
9	September	9	9/30/2022	30	Yes
10	October	10	10/27/2022	27
11	November	11		1
12	December	12		1
Sheet1

Cell Formulas
Range	Formula
C1:C12	C1	=IF(B1<$J$1,EOMONTH(DATE($I$1,B1,1),0),IF(B1=$J$1,$H$1,""))
D1:D12	D1	=IFERROR(DAY(C1),1)
E1:E12	E1	=IF(B1=$J$1,"",IF(AND(B1<$J$1,D2>$L$1),"Yes",""))
H1	H1	=TODAY()
I1	I1	=YEAR(H1)
J1	J1	=MONTH(H1)

 

[My Aswer Is This]

I was just just working off this comment of your:
"Basically trying to create a workbook that has monthly data that will automatically hide each prior month as the year progresses and opens to the current months sheet."

Since you think this will work for you:
You posted: Sheets(Format(Now,"mmm")).Activate
Then it looks like that should work.

 

[muhleebbin]

I was just just working off this comment of your:
"Basically trying to create a workbook that has monthly data that will automatically hide each prior month as the year progresses and opens to the current months sheet."

Since you think this will work for you:
You posted: Sheets(Format(Now,"mmm")).Activate
Then it looks like that should work.

Yeah sorry, I rarely ever explain myself properly the first time. That last statement was trying to explain that as the year progresses that each month will subsequently get hidden that has passed. If we were in March, then January and February would be hidden and since we're in October January - September are hidden. All current months and after remaining visible. The question is primarily can the code that was written in the initial post be written better vs copy/pasting that code 12 times and adjusting the values. Just trying to get better at this whole VBA thing.

 

[My Aswer Is This]

This you said and got me confused for sure:
"It would also keep November and December visible. Cell H1 is a today function and L1 is number of days after a month before that prior month gets hidden. So if the date was 10/10/2022 it would keep September visible but on the 11th it would hide September. With that in mind, based off the day,"

And to just hide all sheets except for this month's sheet would not require any formulas:

This script would hide all sheets if the sheet name were not today's Month
So, for example today is October 27 so only the sheet named "October" would be visible.

Sub Hide_Sheets()
'Modified 10/27/2022  7:54:24 PM  EDT
Application.ScreenUpdating = False
Dim ans As String
ans = Format(Date, "MMMM")

    For i = 1 To Sheets.Count
        If Sheets(i).Name <> ans Then Sheets(i).Visible = False
    Next
Application.ScreenUpdating = True
End Sub

 

[muhleebbin]

This you said and got me confused for sure:
"It would also keep November and December visible. Cell H1 is a today function and L1 is number of days after a month before that prior month gets hidden. So if the date was 10/10/2022 it would keep September visible but on the 11th it would hide September. With that in mind, based off the day,"

And to just hide all sheets except for this month's sheet would not require any formulas:

This script would hide all sheets if the sheet name were not today's Month
So, for example today is October 27 so only the sheet named "October" would be visible.

Sub Hide_Sheets()
'Modified 10/27/2022  7:54:24 PM  EDT
Application.ScreenUpdating = False
Dim ans As String
ans = Format(Date, "MMMM")

    For i = 1 To Sheets.Count
        If Sheets(i).Name <> ans Then Sheets(i).Visible = False
    Next
Application.ScreenUpdating = True
End Sub

This is why i'm clearly not a novelist

The confusing line was because I now provided what was on Sheet1 and what the thought process was for that. The real data that governs hidden sheets are in Range A1:A12 (sheet names) and Range E1:E12 (if the sheet should be hidden or not). H1 is a today function so that as the year progresses it calculates E1:E12 if the month should be hidden or not. The number in L1 is number of days after a month ends where it would keep the previous months sheet hidden or not (in this case 10 days). This way when it's the first of the month, the previous month doesn't automatically get hidden and gives the user the number of days to still work on that previous month. Hopefully this makes a little more sense as to the thought process here.

With your code is there a way to make ans = the range E1:E12 and for each i that is each sheet name (in range A1:A12) if that equals yes for the corresponding E1:E12 that it would hide that sheet or not?

Again the purpose of this is to only hide the months that have passed and all other months to be visible.

Thanks again for your assistance here!

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[muhleebbin]

Thanks for the effort!