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

J15491

New Member
Joined
Jan 10, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,695
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jan 10, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,695
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 
Solution

J15491

New Member
Joined
Jan 10, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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
Worked perfectly. Thank you so much for your help.
 

J15491

New Member
Joined
Jan 10, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Worked exactly as needed. Thank you very much
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,695
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You're welcome. I was happy to help. Thanks for the feedback!
 

Forum statistics

Threads
1,140,922
Messages
5,703,176
Members
421,280
Latest member
Jaycee01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top