title: VBA Validation - multiple date validations on one cell
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>Here is what I have that works to validate the date within the named range:</p>
  
  <pre><code>' 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 " &amp; sd &amp; " and " &amp; ed
  End With
  </code></pre>
  
  <p>That works fine by itself, as does this code to validate the start date is before the end date:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>======================</p>
  
  <p>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.</p>
  
  <p>An overall procedure calls the validation and the main procedure if the user changes the start or end date that they want to view:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Then the two date procedures look something like this:</p>
  
  <pre><code>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 &lt;= ued And ued &lt;= ed) Then
      Debug.Print "ued out of range"
      title = "End Date"
      msg = "You must enter a Start Date within the calendar date range of " &amp; sd &amp; " and " &amp; 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 &lt; 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
  </code></pre>
  
  <p>Am I missing something or am I trying to do something that is just not going to work?</p>