Using an External Control

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:

1. Activate the VBE.

2. Right-click the Toolbox and choose Additional Controls.

Select View Toolbox if the Toolbox is not visible.

3. In the Additional Controls dialog box, scroll down and place a check mark next to Microsoft Date and Time Picker Control 6.0.

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.

Inxait Dalu

DIMHI

CMcgauted

Qoma*) !

mrthl

□ (n«*™»»

■ u-i-xmi.;*

CiirdaTtiijcttMy

□ (rMLHO>i

mwiUfTiHiVHKiflia-B IfiiaKiaiu»

Orifloic _

LrirfTc'cl

CjmyrfTiin-iir

Irjt

tat_ Tdnn _

FmruL

w^i Li

0

Lfft

f

LIPV59S9

»WW*

Vl/lHI

M

tlirfiw-t

P ■

4 nKUl^a^

-

Figure 14-19: The Date and Time Picker Control in a UserForm.

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.

tiiHHrl HrttS

2 ¡2S/2W7

Insert

_lJ February 2007 jJ

1 2 3

4 5 6 J a 9 W)

is is » ;t 2 2S 26 n 25

A 5 (s 7 8 S 10

i^notiair it/:ii/aoo6

Figure 14-20: Inserting a date using the Date and Time Picker Control.

Figure 14-20: Inserting a date using the Date and Time Picker Control.

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- This example, named ® date and time picker. xlsm, is available on the companion ROM CD-ROM.

4 PREV

NEXT

0 0

Post a comment