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 If
Next, 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 If
The 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
Watch It Done On YouTube:
You Might Also Like
To view this protected post, enter the password below:
If you've ever encountered a corrupt Excel workbook, you've probably tried lots of things already, including possibly tearing your hair out. In this particular scenario, we have lots of extra sheets that show up in the Visual Basic Editor. This is not great. ...and it...
Sometimes in Excel, we need to automate simple tasks, like to create sheets automatically for each item in a range. If you just had a few worksheet tabs that needed to be created, it wouldn't be a big deal, but what if you had to create 100 different worksheet tabs?!...
Date Picker Calendar in Excel VBA Oftentimes, users want to click a button and select a date. This is no different for Excel developers. Check out this ActiveX control by Microsoft that allows users to do just that. It's a little old school looking, but actually has...
Check out how Jiyaad is able to save 5+ hours biweekly! He's using the Complete Web Automation with Excel VBA and Selenium course, jam packed with 8+ hours of videos.Want to see Free Previews? Click here.Get Lifetime Access to the course by using coupon code 'EVBAJYD'...
In order to highlight text in a TextBox (other than Tabbing into that field), you can insert a couple helpful snippets of VBA code that will help you accomplish this easily! The first line tells VBA to put the cursor at the leftmost (0) character:...