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:

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:

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

How To Add A Date Picker Calendar in Excel VBA

How To Add A Date Picker Calendar in Excel VBA

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