Date Validation VBA

[Noz2k]

Hey, I've only been using VBA for a couple of days and was wondering how to apply restrictions so that only data of a certain format can be entered into a textbox.

Specifically at the moment I am looking at making it so a textbox can only contain valid dates of the format "dd/mm/yyyy" and if this is not the case I would like a message box to come up as well as setting the focus back to that textbox.

I have tried

Private Sub Text1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Text1.Value <> Format(CDate(Text1.Value), "dd/mm/yyyy") Then
 
        MsgBox "Must Be Format dd/mm/yyyy"
 
        Text1.SetFocus
 
    End If
End Sub

 

[JamesW]

Hi,

This may help:

    If Text1.Value Like "##[/]##[/]####" Then
        MsgBox "yes"
    Else
        MsgBox "no"
    End If

 

[Noz2k]

Thanks, this works for the formatting. However it doesn't set the focus back into the textbox, so if you ignore the warning message then the invalid data stays.

Any idea on how to do this, or failing that clear the data entered?

 

[JamesW]

Hi,

Do you mean like this?

    If Text1.Value Like "##[/]##[/]####" Then
        MsgBox "yes"
    Else
        MsgBox "no"
        Text1.SetFocus
        Text1.Value = ""
    End If

 

[Noz2k]

Yes, that's what I have tried, but the cursor still just moves to the next textbox rather than going back to textbox1

Here is my code

Private Sub Textbox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not Textbox1.Value Like "##[/]##[/]####" Then
        MsgBox "Must Be Format dd/mm/yyyy"
        Textbox1.SetFocus
        Textbox1.Value = ""
 
Else: Textbox2.SetFocus
 
End If
End Sub

 

[Noz2k]

Don't worry, solved it, just needed to add

Cancel = True

after the SetFocus