​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking

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


End Sub

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

End Sub

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")


End Sub

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

End Sub

That's all there is to error free date handling in your VB/VBA apps and the interface is pretty slick too.