Data Validation from a list OR Sunday date?

[bh24524]

Hello, I am looking to kinda revamp one of our Excel files for keeping track of vacation changes for warehouse employees. Here is a simple layout of what the form looks like:

Data is entered in Cells A2(Vacation Week they are Changing From) and A3(Vacation Week they are Changing To). I'd like be able to have both of these cells have a drop-down list where they can select one of the three options in column K OR also allow entry of a Sunday date in either A2 or A3. Is there some way to be able to have both of these? Maybe something other than data validation? I kinda prefer that drop-down though if possible as it keeps entries consistent by having the set choices.

 

[rlv01]

I think you may need to provide a visual example of what you mean by "OR also allow entry of a Sunday date in either A2 or A3". BTW, You can always change the validation Error Alert property to allow you to ignore the validation list and type in what you want.

 

[bh24524]

I think you may need to provide a visual example of what you mean by "OR also allow entry of a Sunday date in either A2 or A3". BTW, You can always change the validation Error Alert property to allow you to ignore the validation list and type in what you want.

View attachment 104636

So basically they should only have 4 choices of what can be entered in A2 and in A - the three choices found in column K or any Sunday date of any year. So if I try to enter a Monday date, it should not allow it. I do have a VBA solution from a while back that someone gave me where they literally can only type in those things or it will give them an error message, but I kinda also want to make it less tedious in case for example they happened to mis-type one of the words and misspelled it. If it's not exactly the correct spelling, the VBA will populate the error. That's why I kinda had the drop-down menu in mind so that they don't have to worry about doing any typing(Except a Sunday date of course) - they just select one of the words from the menu and it will always be correct. Hope that clarifies? Perhaps what you said about the data validation ignoring the validation error could be used in tandem with the VBA solution?

 

[Peter_SSs]

Perhaps what you said about the data validation ignoring the validation error could be used in tandem with the VBA solution?

Along those lines, give this a test.

1. Set up the DV in A2:A3 to allow "List" with the Source being K2:K4 on the Settings tab
2. Optional: Still in the DV dialog, on the Input Message tab enter an Input Message something like this: Choose from the drop-down or enter a Sunday date
3. Still in the DV dialog but on the Error Alert tab, remove the tick from the "Show error alert after invalid data is entered" box
4. Use the Worksheet_Chenge event code below

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim sMsg As String
  
  Set Changed = Intersect(Target, Range("A2:A3"))
  If Not Changed Is Nothing Then
    For Each c In Changed
      Select Case True
        Case IsDate(c.Value) And Format(c.Value, "ddd") = "Sun"
        Case IsNumeric(Application.Match(c.Value, Range(Mid(c.Validation.Formula1, 2)), 0))
        Case Else
          sMsg = sMsg & vbLf & c.Address(0, 0) & vbTab & "(" & c.Value & ")"
          Application.EnableEvents = False
          c.ClearContents
          Application.EnableEvents = True
      End Select
    Next c
    If Len(sMsg) > 0 Then MsgBox "Incorrect value removed from" & sMsg
  End If
End Sub

 

[kvsrinivasamurthy]

select range A2:A3
Apply formula for Data Validation.

=OR(A2="Cancelled",A2="Open",A2="Split",IF(ISNUMBER(A2+0),WEEKDAY(A2,1)=1,))

 

[Peter_SSs]

select range A2:A3
Apply formula for Data Validation.

=OR(A2="Cancelled",A2="Open",A2="Split",IF(ISNUMBER(A2+0),WEEKDAY(A2,1)=1,))

I see at least a couple of issues with that suggestion:

1. It does not provide a drop-down for the user to choose one of the word options as the OP seemed to want:

That's why I kinda had the drop-down menu in mind so that they don't have to worry about doing any typing(Except a Sunday date of course) - they just select one of the words from the menu and it will always be correct.

2. It allows entries that do not seem to fit the requirements. For example, it allows the entry of 1.23

 

[bh24524]

Along those lines, give this a test.

1. Set up the DV in A2:A3 to allow "List" with the Source being K2:K4 on the Settings tab
2. Optional: Still in the DV dialog, on the Input Message tab enter an Input Message something like this: Choose from the drop-down or enter a Sunday date
3. Still in the DV dialog but on the Error Alert tab, remove the tick from the "Show error alert after invalid data is entered" box
4. Use the Worksheet_Chenge event code below

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim sMsg As String
 
  Set Changed = Intersect(Target, Range("A2:A3"))
  If Not Changed Is Nothing Then
    For Each c In Changed
      Select Case True
        Case IsDate(c.Value) And Format(c.Value, "ddd") = "Sun"
        Case IsNumeric(Application.Match(c.Value, Range(Mid(c.Validation.Formula1, 2)), 0))
        Case Else
          sMsg = sMsg & vbLf & c.Address(0, 0) & vbTab & "(" & c.Value & ")"
          Application.EnableEvents = False
          c.ClearContents
          Application.EnableEvents = True
      End Select
    Next c
    If Len(sMsg) > 0 Then MsgBox "Incorrect value removed from" & sMsg
  End If
End Sub

Thank you for this. This should work very nicely. One thing though and this is just for my own curiosity after looking at one of the formulas kvsrinivasamurthy provided as a suggestion. Is it possible to have the 3 different word choices not in the actual worksheet like I have in column K but in the formula itself and make this work? I noticed he put the choices in the formula of the Data Validation itself but I didn't know you could actually do that. Like I said, was just curious about that - it's learning for me. Either way, this works, so I'm glad for that!

​

 

[Peter_SSs]

The issue with a Custom Formula like that for DV is that you lose the drop-down which I thought you wanted?

Another option, rather than use a Custom Formula is to just list the options in the Source box of the List DV option like this.

An advantage of this is it will provide a drop-down.
A disadvantage is that it allows some errors if the values are actually typed in rather than choosing from the drop down. It allows the entry of any number of space characters before/after the actual allowed value. For example, in the mini sheet below where I have used this type of DV, I have typed 2 spaces before and 3 spaces after 'Open' in A3 and the DV has allowed that as a 'valid' entry.

bh24524.xlsm
	A	B
1
2	Open	4
3	Open	9
Sheet3

Cell Formulas
Range	Formula
B2:B3	B2	=LEN(A2)

Cells with Data Validation
Cell	Allow	Criteria
A2:A3	List	Cancelled,Open,Split

 

[bh24524]

The issue with a Custom Formula like that for DV is that you lose the drop-down which I thought you wanted?

Another option, rather than use a Custom Formula is to just list the options in the Source box of the List DV option like this.

View attachment 104658

An advantage of this is it will provide a drop-down.
A disadvantage is that it allows some errors if the values are actually typed in rather than choosing from the drop down. It allows the entry of any number of space characters before/after the actual allowed value. For example, in the mini sheet below where I have used this type of DV, I have typed 2 spaces before and 3 spaces after 'Open' in A3 and the DV has allowed that as a 'valid' entry.

bh24524.xlsm
	A	B
1
2	Open	4
3	Open	9
Sheet3

Cell Formulas
Range	Formula
B2:B3	B2	=LEN(A2)

Cells with Data Validation
Cell	Allow	Criteria
A2:A3	List	Cancelled,Open,Split

Ah okay that is good to know. Yeah I definitely don't want to lose the drop-down menu. I'll leave the entries in column K but just change the font color to white to turn them invisible. It's good to know though about the different options available as well as the ramifications of having the selections typed in the Source in case I might need to use that for a different concept. Good stuff!

 

[bh24524]

Okay wait, something's wrong. I did what you said above, but it's not working. I typed January 6th in but I didn't get a message pop-up about the Sunday date:

I pasted your code in and I set the data validation in for K2:K4 and disabled the show error alert but I was able to type 1/6/24 which isn't a Sunday date but I didn't get a pop up telling me that was wrong. I even entered dddd in A2 and no message from VBA. Did I do something incorrect?