validating to and from dates in user form

[tomanton]

Hi Excel Gurus,
i am trying to make a user form with two msg boxes (To and From Date).
In excel spreadsheet It was rather easy to bring a popup msg if they were invalid under some stated rules. please see below a rationale of the rules where A3=From Date and B3 To Date and the desired outcome of is:

1.=IF(AND(ISBLANK(A3),ISBLANK(B3)),"Supply From and To Dates","")
2.=IF(AND(NOT(ISBLANK(B3)),ISBLANK(A3)),"supply From Date","")
3.=IF(AND(NOT(ISBLANK(A3)),ISBLANK(B3)),"supply To Date","")
4.=IF(NOT(EDATE($A$3,12)>=$B$3),"To date is more than 1 year after From Date",""))
5.=IF(NOT($A3<$B3),"To date needs to be later than From Date"
 
and the all concluding formula:
=IF(AND(ISBLANK($A3),ISBLANK($B3)),"Supply From and To Dates",IF(AND(NOT(ISBLANK($A3)),ISBLANK($B3)),"supply To Date",IF(AND(NOT(ISBLANK($B3)),ISBLANK($A3)),"supply From Date",IF(NOT($A3<$B3),"To date needs to be later than From Date",IF(NOT(EDATE($A3,12)>=$B3),"To date is more than 1 year after From Date","")))))

So i wonder if it is possible to make a VBA code to validate these two dates? and bring a warning msg after keypress or after pressing submitcmd or any other available if available
Thank you

 

[dan_l]

I would add a sub at the beginning of whatever trigger. You can do a bunch of stuff:

if textbox1.value = "" then
msgbox "something"

or select case datediff("d", date1,date2)
case < 0

Same idea----just different!

 

[tomanton]

Dan,
thanks for your reply. Sorry but i am not good at VBA at all, however i read a lot how to go about this issue bu there was no straightforward answer. So can i ask you to provide me more with vba code. i thougth if for To and From dates i would use a helper cells for dates to be inputed and on ccmdOk to be evaluated. Wouldn't be easier?

 

[NARAYANK991]

Hi Tom ,

Try this :

Public Sub Validate_Dates()
          Dim msg As String
         
          With ActiveSheet
                If IsEmpty(.[A3]) And IsEmpty(.[B3]) Then
                  msg = "Supply From and To Dates"
                ElseIf Not (IsEmpty(.[B3])) And IsEmpty(.[A3]) Then
                  msg = "supply From Date"
                ElseIf Not (IsEmpty(.[A3])) And IsEmpty(.[B3]) Then
                  msg = "supply To Date"
                ElseIf Not (Application.WorksheetFunction.EDate(.[$A$3], 12) >= .[$B$3]) Then
                  msg = "To date is more than 1 year after From Date"
                ElseIf Not (.[$A3] < .[$B3]) Then
                  msg = "To date needs to be later than From Date"
                End If
         
                If msg <> "" Then MsgBox msg
            End With
End Sub

Narayan

 

[tomanton]

Narayan,
Thanks for this! i've got a different version of the code from other excel site, which i tried to modify to meet my needs as i need to clear the form after sumbition however dont know to clear combo box and how to clarify that inputing should stop at the row 22:

Dim Ws As Worksheet
Dim MyData As Range
Dim c As Range
Dim rFound As Range
Dim r As Long
Dim rng As Range
Dim ErrMsg As String
  Dim FromDate As Date
  Dim ToDate As Date
  Dim emptyRow As Long
Private Sub CommandButtonSubmit_Click()
 
  If Me.TextBoxFromDate = "" Then
  ErrMsg = ErrMsg & "You must supply From Date" & vbCrLf
  ElseIf Not IsDate(Me.TextBoxFromDate) Then
  ErrMsg = ErrMsg & "From Date """ & Me.TextBoxFromDate & """ is not a valid date." & vbCrLf
  Else
  FromDate = CDate(Me.TextBoxFromDate)
  End If
  
  If Me.TextBoxToDate = "" Then
  ErrMsg = ErrMsg & "You must supply To Date" & vbCrLf
  ElseIf Not IsDate(Me.TextBoxToDate) Then
  ErrMsg = ErrMsg & "To date """ & Me.TextBoxToDate & """ is not a valid date." & vbCrLf
  Else
  ToDate = CDate(Me.TextBoxToDate)
  End If
  
  If ErrMsg = "" Then
  If ToDate <= FromDate Then
  ErrMsg = "To Date needs to be later than From Date"
  ElseIf ToDate > DateSerial(Year(FromDate), Month(FromDate) + 12, Day(FromDate)) Then
  MsgBox "To Date is more than 1 year after From Date", vbExclamation + vbRetryCancel
  End If
  End If
  
  If ErrMsg <> "" Then
  MsgBox ErrMsg
  Else
  
  Set c = MyData.Cells(MyData.Rows.Count, 1).Offset(1)
  Application.ScreenUpdating = False  
  With Me
  c.Value = .OwnerBox.Value
  c.Offset(0, 1).Value = .ContactBox.Value
  c.Offset(0, 2).Value = .TextBoxFromDate.Value
  c.Offset(0, 3).Value = .TextBoxToDate.Value
    
  'clear the form
  ClearControls
  'resize database
  Set MyData = c.CurrentRegion
  End With
  Application.ScreenUpdating = True
  
  End If
End Sub

 

[NARAYANK991]

Hi ,

I am not able to understand which is the combobox , but to clear the combobox , use the statement :

Me.ComboBox1.Text = ""

Can you upload your workbook , since I am not able to understand what you mean by the following :

inputing should stop at the row 22

Narayan

 

[tomanton]

Thanks for you time. have solved that one!