I have a workbook containing 5 worksheets named week 1, week 2, week 3, etc. I only want week 5 to be visible if there are 5 weeks in the month. Cell N3 contains the date 5/1/2021 or whatever month it may be. Any help would be appreciated.
5 tuesdaysCould you define what you consider five weeks. Is that five Mondays, five Tuesdays, five Wednesdays etc. Obviously there is never gong to be five of all the different days of the week in any month.
Your example used 5/1/2021 which was a Saturday. Do you only want this to happen when there are five Saturdays in a month.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target <> Range("N3") Then Exit Sub
Dim ws As Worksheet: Set ws = Worksheets("Week 5")
Dim dt As Date
Dim num As Integer, fd As Integer, mnth As Integer
Dim numdays As Integer
Dim td As Date, fdm As Date
td = Range("N3")
mnth = Month(td)
numdays = Day(DateSerial(Year(td), Month(td) + 1, 1) - 1)
dt = Application.WorksheetFunction.EoMonth(Range("N3"), 0)
num = Weekday(dt)
fdm = mnth & "/1/" & Year(td)
fd = Weekday(fdm)
ws.Visible = False
Select Case fd
Case Is = 1
If numdays = 31 Then ws.Visible = True
Case Is = 2
If numdays >= 30 Then ws.Visible = True
Case Is = 3
If numdays >= 29 Then ws.Visible = True
Case Else
End Select
End Sub
Worked perfectly. Thank you so much for your help.Does this do what you want. Place code in the Worksheet Module of the Sheet where the date will be entered in Cell N3.
VBA Code:Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Target <> Range("N3") Then Exit Sub Dim ws As Worksheet: Set ws = Worksheets("Week 5") Dim dt As Date Dim num As Integer, fd As Integer, mnth As Integer Dim numdays As Integer Dim td As Date, fdm As Date td = Range("N3") mnth = Month(td) numdays = Day(DateSerial(Year(td), Month(td) + 1, 1) - 1) dt = Application.WorksheetFunction.EoMonth(Range("N3"), 0) num = Weekday(dt) fdm = mnth & "/1/" & Year(td) fd = Weekday(fdm) ws.Visible = False Select Case fd Case Is = 1 If numdays = 31 Then ws.Visible = True Case Is = 2 If numdays >= 30 Then ws.Visible = True Case Is = 3 If numdays >= 29 Then ws.Visible = True Case Else End Select End Sub