Make week 5 visible if there are 5 weeks in the month.

[J15491]

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.

 

[igold]

Could 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.

 

[J15491]

Could 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.

5 tuesdays

 

[igold]

Does this do what you want. Place code in the Worksheet Module of the Sheet where the date will be entered in Cell N3.

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

 

[J15491]

Does this do what you want. Place code in the Worksheet Module of the Sheet where the date will be entered in Cell N3.

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.

 

[J15491]

Worked exactly as needed. Thank you very much

 

[igold]

You're welcome. I was happy to help. Thanks for the feedback!

 

[igold]

@smozgur - Thank you very much!