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