Validating Date in Textbox of Userform

[Fherrera]

Hi everyone,

I was hoping there was a simpler way of validating a date in a textbox. (Similar to how in an Excel cell you can enter a date in a variety of formats and it still recognizes the value as a date..(although sometimes it notices non-dates as dates...but still )

I did a keyword search and came up with these two threads:
thread707-603271 and thread707-712307

But, these are for simple validations of either numeric entries or entries between a certain number.

I know, I can validate for a certain date format (yyyy/mm/dd) for example. Not the easiest thing, but still possible. The real question is whether there is some sort of excel function that does the similar thing that I mentioned above (enter date in a variety of formats..)

Thanks.
Frank

 

[VBAjedi]

Try:

If IsDate(x) Then

That recognizes formats like "12/1/03" and "December 1, 2003".

Let me know if that's what you are after!

VBAjedi

 

[Fherrera]

So simple, yet... exactly what I needed Thanks.

I ended up going with this:

Private Sub textStartDate_AfterUpdate()
'
' Error check to make sure date is correct.
'
    If IsDate(textStartDate.Text) Then
        Dim myDate As Date
        myDate = DateValue(textStartDate.Text)
        textStartDate.Text = Format(myDate, "yyyy/mm/dd")
    Else
        MsgBox "Invalid date format"
        textStartDate.Text = "1995/01/01"
'        textStartDate.SetFocus
    End If
End Sub

The thing that's kind of bugging me is that if an invalid date is enterred, the error msg displays and the value resets... But the focus of form is the NEXT text box. I'd like to make it refocus on the current textbox that had an invalid entry... Any idea's? I tried to setFocus it, but that didn't work. Am I using the wrong event?

Thanks.

Frank

 

[PHV]

Put your code in the BeforeUpdate event procedure of textStartDate and set Cancel=True before the MsgBox

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Fherrera]

That worked well! Thank's PHV. I had tried something similar but I had placed cancel=True at the end of the progam ..

Oi. Oh well. Good stuff good stuff.

Thanks again.

 

[Fherrera]

Hi again. I'm still trudging along with my first user interface/form. It looks good Well atleast I like to thinkso.

In any case, with the date verification I have two text boxes textEndDate and textStartDate. Start Date is the left of the End Date textbox and the tabindex is right after it. They are both within a frame. The next tabindex is a combo box which is below this and within a different frame (i'm not sure if any of this info is relevant but perhaps it is so I include it anyway

The problem I have now is when an invalid date is enterred I want to display the warning and then put in a default value and have it be highlighted for easier text entry. For some reason this works with the textStartDate box but not the end date box. (Looking below you can see the code is identical)

When I enter an invalid date in the end date box, I get the error message and nothing is highlighted, I can't even see the cursor or even a control in "focus". If i hit the up arrow key it selects the end date box and if i hit the down arrow key it selects the combo box below it.

Can anyone explain why/help me fix this?

Private Sub textEndDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'
' Error check to make sure date is correct.
'
    If IsDate(textEndDate.Text) Then
        Dim myDate As Date
        myDate = DateValue(textEndDate.Text)
        textEndDate.Text = Format(myDate, "yyyy/mm/dd")
    Else
        Cancel = True
        MsgBox "Invalid date format"
        With textEndDate
            .Text = Format(Date, "yyyy/mm/dd")
            .SelStart = 0
            .SelLength = Len(.Text)
        End With
    End If
    
End Sub

Private Sub textStartDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'
' Error check to make sure date is correct.
'
    If IsDate(textStartDate.Text) Then
        Dim myDate As Date
        myDate = DateValue(textStartDate.Text)
        textStartDate.Text = Format(myDate, "yyyy/mm/dd")
    Else
        Cancel = True
        MsgBox "Invalid date format"
        With textStartDate
            .Text = "1995/01/01"
            .SelStart = 0
            .SelLength = Len(.Text)
        End With
    End If
End Sub