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

#### J15491

##### New Member
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
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
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
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``````

#### J15491

##### New Member

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
Worked exactly as needed. Thank you very much

#### igold

##### Well-known Member
You're welcome. I was happy to help. Thanks for the feedback!

Replies
6
Views
215
Replies
14
Views
237
Replies
15
Views
64
Replies
1
Views
73
Replies
4
Views
62

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.

### Which adblocker are you using?

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

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