Bullet proof date input in VB/VBA userforms
The secret to bullet proofing the input of dates in VB/VBA userforms is to not allow the user to enter dates manually in the first place. That means using some kind of calendar pop-up. I've found that the MonthView built-in control is perfectly adequate for this sort of thing. The best way to do this was to use a textbox in combination with an image control with a picture of a down arrow on it. If you put them together it looks just like a drop-down list box control (see figure below)
Now when the user clicks on the button (image), instead of a drop-down list appearing simply display your popup calendar like below.
I put my calendar on a separate userform because I wanted to also include a CLEAR button that would clear out any date already entered in the text field.
Now, trivially, the VBA code behind clicking the drop-down "button" is simply:
Private Sub Image1_Click()
xpos = UserForm1.start_date.Left + UserForm1.start_date.Width
ypos = UserForm1.start_date.Top + UserForm1.start_date.Height
Xpos and ypos are just two global variables which userform2 uses to position itself near to the date input text box in it's initialization code viz.
Private Sub UserForm_Initialize()
UserForm2.Move xpos, ypos
Now all we have to deal with is when the user clicks on a date. This is returned in the dateclicked in-built identifier
Private Sub MonthView1_DateClick(ByVal dateclicked As Date)
UserForm1.start_date.Value = Format(dateclicked, "dd-MMM-yyyy")
The format string can obviously be changed to suit your own tastes. Finally we have to prevent the user from entering their own (potentially error prone) dates into the input box via the keyboard. The code below does this:
Private Sub start_date_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
KeyAscii = 0
That's all there is to error free date handling in your VB/VBA apps and the interface is pretty slick too.
A selection of topics on IT and its application to finance.
Send me your comments, questions or suggestions by clicking here