GET ULTIMATE EXCEL

March 29, 2018

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 event, which will trigger every time anything in that text box changes, including keystrokes, backspace, etc.…

We need to change this to a key down event, because the key down event triggers every time a key is entered, however it also captures which keys were pressed. That’s how we can determine whether the up arrow or the down arrow is pressed.

In the video below, you’ll see that we determined which key was pressed by debugging a few times using a breakpoint or the word “stop”, but eventually we determined that the keycode for the up arrow was 38 and the down arrow was 40.

The following IF THEN statement allows us to update the value of the text box based on whether the up arrow key was pressed or the down arrow key was pressed:

Private Sub tbDate_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = 38 Then 'up arrow
        
ElseIf KeyCode = 40 Then 'down arrow
    
End If

End Sub

We then determined that we can take the current text box (me.tbDate) and make it equal to itself plus one if the up arrow key was pressed. We also need to take the current date field and decrease it by one day if the down arrow key was pressed. We accomplish this by converting the string dates into actual dates using the CDate() function, which converts a string that looks like a date to an actual date format that is able to perform calculations.

We also created a label that we formatted in order to see the weekday every time a keystroke occurred. In the video, we had a little fun and we decided to also put the left and right arrow keys to work, incrementing or decrementing a week at a time!

Here’s the final code:

Private Sub tbDate_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)



If KeyCode = 38 Then 'up arrow
    Me.tbDate = CDate(Me.tbDate) + 1
    
ElseIf KeyCode = 40 Then
    Me.tbDate = CDate(Me.tbDate) - 1
    'make textbox have focus again
    Me.tbDate.SelLength = Len(Me.tbDate)
    Me.tbDate.SelStart = Len(Me.tbDate) - 1
    Me.tbDate.SetFocus
    
ElseIf KeyCode = 37 Then 'left
    Me.tbDate = CDate(Me.tbDate) - 7
    
ElseIf KeyCode = 39 Then 'right
    Me.tbDate = CDate(Me.tbDate) + 7
End If

Me.lblWeekday = Format(CDate(Me.tbDate), "dddd")


End Sub
Private Sub UserForm_Initialize()

Me.tbDate = Date
Me.lblWeekday = Format(CDate(Me.tbDate), "dddd")
End Sub

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

2 comments on “Increase TextBox Date Using Up and Down Arrow Keys In Excel VBA”

  1. Thank you so much. Sorry to ask
    1. I enter records directly on to the worksheet and use column “A” to enter the dates. Therefore the Field headings go into row 1.
    Then cell A2 will begins with today’s date as a default which can be edited using the arrow keys as you showed. For the next record Cell A3 will then take either this new date in A2 or the next date which can again be edited using the arrow keys such that it is ready for this next record and so on.
    2. On re-opening the spreadsheet it continues from the next blank row with today’s default date. Any help thanks.
    3. How can we advance by a month or the month part of the date instead of 7 days.

Excel VBA Is Fun

We believe Excel is one of the most versatile programs out there - and learning to program and automate it makes you very valuable!

You can prevent wasted time and errors, speed up tasks and become indispensable to your organization. 

See how our trainees have gotten raises, promotions and bonuses using their Excel Automation skills while building real problem-solving programs in Excel!
LEARN MORE
Copyright © 2022 ExcelVbaIsFun
X

Forgot Password?

Join Us

0
    0
    Your Cart
    Your cart is emptyReturn to Shop
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram