The example in this section uses the Microsoft Date and Time Picker Control. Although this is not an Excel control (it's installed with Windows), it works fine in a UserForm.
To make this control available, add a UserForm to a workbook and follow these steps:
Activate the VBE.
Right-click the Toolbox and choose Additional Controls.
Select View Toolbox if the Toolbox is not visible.
In the Additional Controls dialog box, scroll down and place a check mark next to Microsoft Date and Time Picker Control 6.0.
Click OK.
Your Toolbox will display a new control.
Figure 14-19 shows the Date and Time Picker Control in a UserForm, along with the Property window. The Format property determines whether it works with dates or times.
Figure 14-20 shows this control being used. Clicking the drop-down button displays a calendar. When the user clicks a calendar date, that date is displayed in the control and is assigned to the Value property for the control. This dialog box is displayed modeless, so the user can select a new cell without closing the dialog box.
When the UserForm is displayed, the Date and Time Picker displays the current date by setting its Value property in the UserForm_Initialize procedure:
Private Sub UserForm_Initialize() DTPicker1.Value = Date End Sub
The code to handle the Insert button click is as follows :
Private Sub InsertButton_Click() ActiveCell = DTPicker1.Value ActiveCell.Columns.EntireColumn.AutoFit End Sub
CD-ROM | This example, named date and time picker.xlsm , is available on the companion CD-ROM. |