Select Page

Enter Textbox Date In Excel Without The Slash – Type Only The Numbers

by | Mar 21, 2018 | ActiveX Controls, UserForms | 1 comment

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:

Download Workbook

**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 (Subroutine) in order to paste the code below and use it. Start here ==>Click here for VBA Basics.**

You Might Also Like

Create Sheets For Each User From Column A

Create Sheets For Each User From Column A

by | May 5, 2020 | , | 0 Comments

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?!...

How To Add A Date Picker Calendar in Excel VBA

How To Add A Date Picker Calendar in Excel VBA

by | Aug 26, 2019 | | 6 Comments

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...

Related posts:

  1. How To Grab All Excel Sheet Names Into Listbox, Grab Column Headers Into Listbox
  2. Excel VBA Userform Basics #1
  3. Increase TextBox Date Using Up and Down Arrow Keys In Excel VBA
  4. How To Get Better Looking Buttons in Excel

1 Comment

  1. ElBey Abdelouahab
    ElBey Abdelouahab on August 16, 2018 at 2:34 pm

    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

    Reply

Submit a Comment

Please Login to post a comment

X

Forgot Password?

Join Us

Discover the Secrets of Excel Your Coworkers Don't Want You To Know and Your Boss Will Love You For...

Free video training will be sent to your email

You have Successfully Subscribed!

0
    0
    Your Cart
    Your cart is emptyReturn to Shop
    Continue Shopping