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
You Might Also Like
To view this protected post, enter the password below:
If you've ever encountered a corrupt Excel workbook, you've probably tried lots of things already, including possibly tearing your hair out. In this particular scenario, we have lots of extra sheets that show up in the Visual Basic Editor. This is not great. ...and it...
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?!...
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...
Check out how Jiyaad is able to save 5+ hours biweekly! He's using the Complete Web Automation with Excel VBA and Selenium course, jam packed with 8+ hours of videos.Want to see Free Previews? Click here.Get Lifetime Access to the course by using coupon code 'EVBAJYD'...
In order to highlight text in a TextBox (other than Tabbing into that field), you can insert a couple helpful snippets of VBA code that will help you accomplish this easily! The first line tells VBA to put the cursor at the leftmost (0) character:...