**New To VBA?: In these Excel VBA Posts, you'll need to Open The Visual Basic Editor (VBE), and Make a New Module and then Create A New Macro (Sub procedure) in order to paste the code below and use it. Please click the above Tool Tips in this paragraph in order to understand these simple concepts instantly.**
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:
1 2 3 4 | 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:
1 2 3 4 5 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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
Increase TextBox Date Using Up and Down Arrow Keys In Excel VBA
We had a question today regarding how to increase or decrease the date field in this Excel VBA user form. The first thing we had to do was create a user form and insert a Text Box. When we double-click on this text box, the default event is the change...
How To Prompt For A File In Excel VBA
**New To VBA?: In these Excel VBA Posts, you'll need to Open The Visual Basic Editor (VBE), and Make a New Module and then Create A New Macro (Sub procedure) in order to paste the code below and use it. Please click the above Tool Tips in this paragraph in order to...
6 Oclock and 12 Pm Don’t Appear Correctly on Userform
Sometimes using a text box on a user form or on a worksheet doesn't work exactly the way we think it should. This is especially true when were working with dates and times straight from a worksheet. The problem we faced when helping Emil was that the format function...
Loop Through Multiple Files in a Folder and Scrape Data From Each
Sometimes using loops with the fileSystemObject in Visual Basic isn't the most intuitive thing to use, but in this article I want to show you some basic things you can do with it. Without getting too technical, the purpose of this article is to show you how you can...
Excel VBA Format Textbox Hours and Minutes for Start Time and End Time
Recently, I was asked how to format Textboxes as Hours and Minutes, like a Start Time and End Time, here's a quick video tip I replied with. Hope you enjoy! https://www.youtube.com/watch?v=HaPAPweKsOM Get the workbook here...
How To Grab All Excel Sheet Names Into Listbox, Grab Column Headers Into Listbox
This week, I was asked how to load the sheet names onto a listbox and then when a sheet in that listbox is clicked - how to list the column headers in the second listbox! Sound crazy hard? No, it's actually very easy. Check out the video below and Download the...
