Prevent anything but a Sunday date from being selected

[bh24524]

Hi so I am wondering if there is any feature or other that I can utilize in an excel sheet that will make it so that employees when filling out a particular field in the excel form, have to put a Sunday date in a particular cell. I want it to prevent them from putting any other date in that week - it must absolutely be a Sunday date in current year. Is there something that can be used for this?

 

[Fluff]

You can use Data Validation for that.
In DV select "Custom" & use this formula
=AND(YEAR(G3)=2019,WEEKDAY(G3,1)=1)

Change G3 to the cell with the DV

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. I assumed the cell with the date is cell A1. Change this cell in the code (in red) to suit your needs. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("[COLOR="#FF0000"]A1[/COLOR]")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Weekday(Target) <> 1 Then
        MsgBox ("You must enter a 'Sunday' date.")
        Target.ClearContents
    End If
    Application.EnableEvents = True
End Sub

 

[bh24524]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. I assumed the cell with the date is cell A1. Change this cell in the code (in red) to suit your needs. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("[COLOR=#FF0000]A1[/COLOR]")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Weekday(Target) <> 1 Then
        MsgBox ("You must enter a 'Sunday' date.")
        Target.ClearContents
    End If
    Application.EnableEvents = True
End Sub

Thank you so much, that will work PERFECTLY!

 

[mumps]

You are very welcome.

 

[bh24524]

You can use Data Validation for that.
In DV select "Custom" & use this formula
=AND(YEAR(G3)=2019,WEEKDAY(G3,1)=1)

Change G3 to the cell with the DV

Thanks for this solution, for some reason i did not see it when i clicked the link in my email, but I can definitely present this as an option as well.

 

[Fluff]

You're welcome & thanks for the feedback.

When you click the link in the email, it will take you to the last post in the thread, so it's always a good idea to scroll up & check you haven't missed anything.