VBA Validation - multiple date validations on one cell

Question

I am trying to validate a date entered in a cell but I am running into a problem. The scenario is I have a named range of cells (column headers) that hold dates as on a calendar view. The user then enters a start and end date and I want to validate: 1) the start date is within the dates in the named range 2) the start date is before the end date.

If the date is invalid, the user should be presented an error message and should have to correct the problem. I have been able to get each validation to work by itself, but not together.

Here is what I have that works to validate the date within the named range:

' Variables for the start and end dates of the calendar range
Dim sd As Date
Dim ed As Date

' Set the dates to the start and end of the calendar range
sd = DateValue(Range("weeks_rg")(1))
ed = DateValue(Range("weeks_rg")(Range("weeks_rg").Columns.Count))

' Validate that the start date is within the calendar range
With Range("start_dt").Validation
 .Delete
 .Add Type:=xlValidateDate, _
 AlertStyle:=xlValidAlertStop, _
 Operator:=xlBetween, Formula1:=sd, Formula2:=ed
 .ErrorTitle = "Start Date"
 .ErrorMessage = "You must enter a Start Date within the calendar date range of " & sd & " and " & ed
End With

That works fine by itself, as does this code to validate the start date is before the end date:

With Range("start_dt").Validation
 .Delete
 .Add Type:=xlValidateDate, _
 AlertStyle:=xlValidAlertStop, _
 Operator:=xlLessEqual, Formula1:=Range("end_dt")
 .ErrorTitle = "Start Date"
 .ErrorMessage = "You must enter a Start Date that is before the End Date"
End With

However, I can't seem to get them to work together. I have tried putting them in separate subs and calling them in sequence, but this doesn't work; only the second validation occurs and the first seems to be ignored. I also tried combining them into a single With statement but that also ignored the first validation.

I'm sure this is something small as I am very new to VBA...and have only gotten this far due to the great help on this site :) Any ideas on what I can do to make this work?

======================

Added 11/16: I've looked through a lot of online help on the functions but can't find much that seems to do what I am trying to do. I'd like to use something like the code below but it doesn't work and I don't know why. None of the validations seem to fire, even though the debug statements show that the logic is working.

An overall procedure calls the validation and the main procedure if the user changes the start or end date that they want to view:

Private Sub Worksheet_Change(ByVal Target As Range)
' This section calls the process utilization procedure when the user moves
' out of the start date or end date fields

    If Target.Address = Range("start_dt").Address Then
        Debug.Print "start changed"
        Range("start_dt").Validation.Delete
        Process_Utilization
        Verify_start_date
    End If

    If Target.Address = Range("end_dt").Address Then
        Debug.Print "end changed"
        Range("end_dt").Validation.Delete
        Process_Utilization
        Verify_end_date
    End If

End Sub

Then the two date procedures look something like this:

Private Sub verify_end_date()

' Variables for the start and end dates of the calendar range
Dim sd As Date
Dim ed As Date
' Variables for the user-entered start and end dates
Dim ued As Date
Dim usd As Date

' create string variable to store our custom formula
Dim title As String
Dim msg As String

Debug.Print "start"

' Set the dates to the start and end of the calendar range
sd = DateValue(Range("weeks_rg")(1))
ed = DateValue(Range("weeks_rg")(Range("weeks_rg").Columns.Count))

' Set the user-entered dates
usd = DateValue(Range("start_dt"))
ued = DateValue(Range("end_dt"))

' Check if the user-entered end date is within the calendar range
If Not (sd <= ued And ued <= ed) Then
    Debug.Print "ued out of range"
    title = "End Date"
    msg = "You must enter a Start Date within the calendar date range of " & sd & " and " & ed

    With Range("end_dt").Validation
     .Delete ' needed to delete any existing validation
     .Add Type:=xlBetween, _
     AlertStyle:=xlValidAlertStop, _
     Formula1:=sd, Formula2:=ed
     .ErrorTitle = title
     .ErrorMessage = msg
    End With

ElseIf ued < usd Then ' Check if the user end date is prior to the user start date
    Debug.Print "end before start"
    title = "End Date"
    msg = "The End Date must be later than the Start Date"

    With Range("end_dt").Validation
     .Delete ' needed to delete any existing validation
     .Add Type:=xlgretaerequal, _
     AlertStyle:=xlValidAlertStop, _
     Formula1:=usd
     .ErrorTitle = title
     .ErrorMessage = msg
    End With

End If


End Sub

Am I missing something or am I trying to do something that is just not going to work?

Answer 1

Here is your code snippet modified to do a custom validation. You were doing an excel built-in Date validation, which as you noticed can only validate with one condition.

To use multiple conditions in excel validation you, need to use custom validation (xlValidateCustom instead of xlValidateDate). When you use this type you will need to create a formula and assign it to the "Formula1" property.

As you can see, to use multiple conditions, I simply used a custom formula with the '=and(condition1,condition2[,...])' function. The '=and()' function will return true only if all conditions return true.

'=and()' function documentation

' Variables for the start and end dates of the calendar range
Dim sd As Date
Dim ed As Date 
' create string variables to store range addresses
Dim sdRange as string
Dim edRange as string
Dim start_dtRange as string
Dim end_dtRange as string
' create string variable to store our custom formula
Dim formula as string

' Set the dates to the start and end of the calendar range
sd = DateValue(Range("weeks_rg")(1))
ed = DateValue(Range("weeks_rg")(Range("weeks_rg").Columns.Count)) 
' store the range addresses of named ranges
sdRange = Range("weeks_rg")(1).Address
edRange = Range("weeks_rg")(Range("weeks_rg").Columns.Count).Address
start_dtRange = Range("start_dt").Address
end_dtRange = Range("end_dt").Address
' store our custom formula in a string
formula = "=and(" & start_dtRange & ">" & sdRange & "," & start_dtRange & "<" & edRange & "," & start_dtRange & "<" & end_dtRange & ")"

' Validate that ('start date' > sd and < ed and < 'end date')
With Range("start_dt").Validation
 .Delete ' needed to delete any existing validation
 .Add Type:=xlValidateCustom, _
 AlertStyle:=xlValidAlertStop, _
 Formula1:=formula
'Modify ErrorTitle and ErrorMessage to have appropriate content
 .ErrorTitle = "Start Date"
 .ErrorMessage = "You must enter a Start Date within the calendar date range of " & sd & " and " & ed
End With