Sometimes you might have a client or you yourself may have the need for a date text box where the user wants to type in only the numerals for a date, and none of the slashes.
The first thing we need to do is create a user form with a single text box in it.
Double-click on the text box in order to edit the code for the text box.
In the video below, you’ll see that we want to use the KeyDown event rather than the change event. This is because the key down event will be able to distinguish between the backspace key and other keys, which is important when we want to trap the backspace key later on.
We start off by triggering an IF THEN statement, when the user has a textbox length of 2 and another keystroke triggers it again. If the Length of the textbox is 2, then we want to add a slash:
If Len(Me.TextBox1) = 2 Then
'add a slash
Me.TextBox1 = Me.TextBox1 & "/"
End IfNext, we find out if the backspace key is pressed using the KeyCode parameter. If KeyCode is the backspace (vbKeyBack), then we want to test whether we have a length of 4, such as “12/1”. If we had “12/1” in the textbox and pressed backspace, we want it to remove the 1 and the slash, resulting in “12”, aka the leftmost 2 characters. We then make the KeyCode = False so the backspace will be cancelled. Thus:
If KeyCode = vbKeyBack Then
If Len(Me.TextBox1) = 4 Then Me.TextBox1 = Left(Me.TextBox1, 2) 'get leftmost 2 characters
KeyCode = False
End IfThe final code includes testing for whether the backspace key is pressed on a length of 4 or 7, and tests whether a numeric keystroke appears on character 2 or 5 in order to auto-place the spacebar:
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyBack Then
If Len(Me.TextBox1) = 4 Then
Me.TextBox1 = Left(Me.TextBox1, 2)
KeyCode = False
ElseIf Len(Me.TextBox1) = 7 Then
Me.TextBox1 = Left(Me.TextBox1, 5)
KeyCode = False
End If
Else
If Len(Me.TextBox1) = 2 Or Len(Me.TextBox1) = 5 Then
'add a slash
Me.TextBox1 = Me.TextBox1 & "/"
End If
End If
End Sub
the vba doesn't like the ";" in the line 13, by the way if you happen to see this (you I mean the one who makes ExcelVBAisFun videos on youtube not some other moderator), I would like to contact you, thank you