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.

Start by creating a userform and enabling the control by Right-clicking on the Tools menu and click Add additional tools

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:

Watch It Done On YouTube:

X

Forgot Password?

Join Us