Need A date Mask format for a Textbox on UserForm

  • Thread starter Thread starter soibien
  • Start date Start date
S

soibien

Is there an Example on howto make a Textbox with Date Mask very like
MS Access with "__/__/____".
I need its so much for my working Daily.

Tks for your help.
 
You can use the Exit event to test the text of the TextBox with code like:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim D As Date
On Error Resume Next

If Not (Me.TextBox1.Text Like "##/##/####") Then
Cancel = True
' 'MsgBox "Invalid Date"
Me.Label1.Caption = "Invalid Date"
Exit Sub
Else
Me.Label1.Caption = "Date Is OK"
End If

End Sub

This tests strictly for the format "##/##/####" where "#" is a numeric
character. If you want to accept any valid date string, use

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim D As Date
On Error Resume Next

D = DateValue(Me.TextBox1.Text)
If Err.Number <> 0 Then
Me.Label1.Caption = "Invalid Date"
Cancel = True
Else
Me.Label1.Caption = "Date Is OK"
End If
End Sub

I often allow for skipping validation completely, sort of a back door, by
testing for the SHIFT key.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim D As Date
On Error Resume Next

' If Not (Me.TextBox1.Text Like "##/##/####") Then
' Cancel = True
' 'MsgBox "Invalid Date"
' Me.Label1.Caption = "Invalid Date"
' Exit Sub
' Else
' Me.Label1.Caption = "Date Is OK"
' End If

If IsShiftKeyDown(LeftOrRightKey:=LeftKeyOrRightKey) Then
Me.Label1.Caption = "Validation Skipped"
Exit Sub
End If

D = DateValue(Me.TextBox1.Text)
If Err.Number <> 0 Then
Me.Label1.Caption = "Invalid Date"
Cancel = True
Else
Me.Label1.Caption = "Date Is OK"
End If
End Sub

The IsShiftKeyDown function can be found at
www.cpearson.com/Excel/KeyTest.aspx.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Back
Top