.woocommerce form .form-row .required{visibility:visible}
Taking too long? Close loading screen.
Excel VBA Is Fun - "Make Excel Work For You"

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

You Might Also Like

Excel VBA UserForms #2

Excel VBA UserForms #2 Create a UserForm with 2 option buttons:  1 Sort by Employee ID or 2 Sort by Name. Part one, create the UserForm and give it buttons. First, bring up the VBA UserForm editor (F11), and add a new UserForm  using the...

Excel VBA Userform Basics #1

UserForm Basics #1 Here, you'll learn to make a UserForm, add a button, and make clicking on that button hide the UserForm! Make a UserForm: Press Alt-F11 to bring up Visual Basics menu Click the drop down menu in the upper left hand and select...

Password Reset
Please enter your e-mail address. You will receive a new password via e-mail.

Pin It on Pinterest

Share This

Share This

Share this post with your friends!

default-poup