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 quite a nice feel to it.
Now, let’s add this to the userform!
In the downloadable workbook, you’ll see the control was renamed to ‘fCal’. When you double-click the control you’ll see the following code which is the DateClick event of that control:
Private Sub fCal_DateClick(ByVal DateClicked As Date) For Each uf In VBA.UserForms If uf.Name = Cal.lblUF Then For Each ctl In uf.Controls If ctl.Name = Cal.lblCtrlName Then 'found the control to give the date to ctl.Value = DateClicked Me.Hide Exit Sub End If Next ctl End If Next uf End Sub
This userform cleverly has two labels to store relevant info on the Userform that summoned it. 1.) The name of the userform that called it and 2.) The name of the control or textbox that needs the date sent to it.
Then, this code above loops through all userforms in your project until it finds one that matches the label for the Userform (lblUF) and the label for the textbox needed (lblCtrlName).
Also, you may need to enable Microsoft Windows Common Controls -2 6.0 (SP6) by using Tools->References and clicking: