3

I have an excel form that I created in combination with VBA. In the form I have a textbox in which the user is to type in a date.

I have created this VBA clause to ensure the user types in a date that supports the format xx/xx/xxxx.

If Not IsDate(textboxDate1.Text) Then
  Call MsgBox("Please select a correct Date format", vbOKOnly)
  Exit Sub
End If

However, with this VBA code, the user is required to enter a date, whether the user needs to or not. So when I have a 4 other textboxes to input a date in my form, and the user only needs to enter in 1 date, and not 5, I have the problem where the user is required to put in a date for the other four textboxs in order submit the form.

So my question: What VBA code is available to first determine whether text exists in the textbox, and then second to determine whether the date is in the correct format or not.

I was trying something similar to this:

 If textboxDate1.ListIndex = -1 Then

but I couldn't get it to work with the IsDate clause.

Many thanks in advance.

Paolo Bernasconi
  • 2,010
  • 11
  • 35
  • 54

1 Answers1

4
If (Len(Trim(textboxDate1.Text)) <> 0)  And Not IsDate(textboxDate1.Text) Then 
  Call MsgBox("Please select a correct Date format", vbOKOnly) 
  Exit Sub 
End If
rene
  • 41,474
  • 78
  • 114
  • 152
  • 1
    + 1 :) However a suggestion. You might want to use `Len(Trim(textboxDate1.Text)) <> 0` instead of `textboxDate1.Text <> ""` – Siddharth Rout Aug 08 '12 at 12:06
  • +1 however, you can optimize this by checking length first. If `Len = 0` there is no point in checking `IsDate`. – JimmyPena Aug 08 '12 at 15:03