I was working on a new tool for the Word uTIlities, when I discovered that there really doesn’t seem to be particularly much available by the way of good information on how to use the date picker control. Not even the two “standard” works I have on my bookshelf (John Walkenbach’s Excel 2010 Power Programming with VBA and Bovey, Wallentin, Bullen and Green’s Professional Excel Development) make much mention of it.
So, while this is not intended to become the be-all and end-all of your search for information on the date picker, I do want to add here what I have learned so far.
Why use the date picker?
In all honesty, getting date and time information from users can be done with standard tools like text boxes or combo boxes. But the date picker offers a large number of advantages, and the main reason for using it is the huge convenience it offers. Firstly, it is more convenient for your users, as it offers them date/time specific ways of providing information (e.g., a “digital clock” for time, and a calendar for dates), while still allowing keyboard folks like me to type in the information they want to add. And convenience for users is not a frill, but a necessity.
Having said that, the date picker also offers huge advantages for the programmer. First and foremost is that it delimits the information users can add, so that you can by definition not get invalid dates or times from your users, obviating a large amount of code that would otherwise be required to test and validate what they have entered. Furthermore, it can be customised to let users enter only the information you want (more on that below). And finally, its values are already in the date format (which in VBA is date/time), so it both accepts and provides date/time information without the need for conversion.
Getting the date picker
Finding the date picker is not obvious. In the VBE form editor, right-click on the toolbox and choose Additional Controls… (Figure 1).
Figure 1 Additional controls for the Toolbox
Then, in the Additional Controls dialog, find the Microsoft Date and Time Picker Control and make sure that the box next to it is marked (just clicking on the control doesn’t mark the box, it just selects the control—you have to click the box itself), as shown in Figure 2.
Figure 2 Additional Controls dialog (Showing Microsoft Date and Time Picker Control)
Click OK, and the control will appear on the Toolbox (Figure 3).
Figure 3 Date Picker added to Toolbox
Now you can click on the tool, and add date pickers to your user forms as you would any other control. Figure 4 shows a new tool I am working on for the Word uTIlities (this will be version 1.9), although I am not certain that all the options shown here will appear in the final version. This is suddenly quite a heavy usage of date pickers—eight at the moment (the three on the right might not make it, though…). Note the three different applications of the date pickers. Some are used to give dates, some for times, and one (bottom right) for only hours.
Figure 4 User form showing various date picker controls
Customising the date picker
I will limit this section to things specific to the date picker. Since it is a form control, it shares many of the normal form control formatting settings, which I will not discuss.
To set how the control works, it is best to work with the Properties pane. Figure 5 shows the Properties pane for a date picker control, with the relevant items highlighted.
Figure 5 Date picker properties
Note that I haven’t highlighted the Calendar color settings, but these are only for window dressing, and should be easy to figure out if you should choose to modify them.
Here’s a quick rundown of what you can do with these properties:
Use MaxDate and MinDate to set (respectively) the latest and earliest dates from which the users can choose (the default range of 1601 to 9999 might be a bit broad…).
The Value property allows you to choose what value the date picker displays when the form is loaded. See below for more details on changing this at run time.
The most important settings are the Format and CustomFormat settings.
For Format, Figure 6 shows that you have four choices: Long date (0); Short date (1); Time (2); and Custom (3).
Figure 6 Format property settings
Figure 7 shows what each of the first three look like (Short date on the left, Long date in the middle, and time on the right).
Figure 7 Date picker formats
Note that time defaults to a 12 hour clock with hours, minutes, and seconds.
So what if you don’t want one of those formats (for example, you would want date and time, or you don’t want seconds, or you want a 24 hour clock, etc.)? In these instances, choose the last format—Custom (3). Then, in the CustomFormat property, define the format you want. I am not going to go into an extended discussion of the formats now—if you are familiar with number formatting (e.g., in Excel, or in Word Merge fields), you will understand what is required. So, for example, to leave out the seconds, use hh:mm. To leave out the seconds, and switch to a 24 hour clock, use HH:mm.
But there are problems. Figure 8 shows a date picker set to the Time (2) format on the left, and one using the Custom (hh:mm) format on the right. The problem, as is patently obvious, is that when the custom format is set, the spin buttons are removed (they are quite important for a time picker) and the drop-down again gives the calendar (essentially rendering it useless for setting a time).
Figure 8 Standard time format vs. custom time format in date picker
So what to do? Well, if you’ve been observant, there’s one highlighted property in Figure 5 that I haven’t discussed—UpDown. That’s what turns on the spin button (and removes the calendar) for the time picker.
Furthermore, the Value, MinDate, and MaxDate properties remain set to dates, even though the custom format specifies only time, although that is not really a problem—just type a time in, and the VBE will accept it.
Also take note of the (Custom) “property”—note that it is in parentheses in the properties pane, indicating that this is not actually a property. Sure enough, click in the property value field, and the dialog launcher appears (Figure 9).
Figure 9 (Custom) date picker “property”
Click the dialog launcher, and the Property Pages dialog appears. I will not discuss the Font, Color, or Picture tabs. But let’s take note of some of the settings on the General tab. Many of these are in the Properties pane ad well, but you can set them here if you wish.
Figure 10 Property Pages dialog
How the date picker works for users
The beauty of the date picker tool is evident when the form is loaded and the user has to set a value. Clicking on the date picker reveals a calendar (Figure 11) that users can navigate (note the previous and next month selectors at the top) until they get to the right value. There, they can click on the date they want, and it is set as the value in the control. And, of course, the picker only presents users with valid dates to choose from. And if users type in invalid dates (e.g., 2014/02/29), the control won’t accept them.
Figure 11 Using the date picker
Times are also easy to set (Figure 12). The user clicks on the time part they want to set (e.g., in Figure 12, the minutes section is clicked). Then the user uses the spin button to move the value up or down. If it reaches the maximum allowed for that time unit (e.g., 59 for minutes or seconds), it just moves on to the minimum again, and vice versa. Users can also type in times if they want.
Figure 12 Using the time picker
Setting the date picker values
One of the nice things about the date picker is the ease with which you can set its values when you load your form (or at any other event, such as when another control on the form is set—the form in Figure 4 does exactly that). For example, for some unknown reason, the value property of the date picker is set to the date you add it to the form. But this means that, for example, a year hence, the date displayed initially by your form will be a year old, when you would probably want it to display the same date on which the user has launched the form.
So what do you do if you want to load specific values that will be determined at run time? The date picker makes this incredibly easy, since it accepts date values. So, for example, some of the underlying code for the form shown in Figure 4 is:
Dim DateTimeFirst As Date […] DateTimeFirst = rev.Date […] If Left(Me.CBxDateSelector1.Value, 6) = "Before" Then Me.DTPicker1 = DateTimeLast Me.TMPicker1 = DateTimeLast
Note that I set the date-formatted date picker and the time-formatted date picker to the same variable (the Date data type in VBA stores both date and time), but each date picker uses only the portion relevant to its formatting—the date for the former, the time for the latter. Of course, you could make doubly sure, or convert variables of other types (like string variables) to date or time values like this:
Me.DTPicker1 = DateValue(DateTimeFirst) Me.TMPicker1 = TimeValue(DateTimeFirst)
Which of course also means that you could specify something like:
Me.DTPicker1 = DateValue("2014/04/01") Me.TMPicker1 = TimeValue("14:05")
Although why you would want to hard-code values like that and not add them manually as defaults is beyond me.
One last thing: to set the date or time to the current (date or time), simply use
Me.DTPicker1 = Now () Me.TMPicker1 = Now ()
Getting date picker values
Opposite to the above, once the user has entered a value in the date picker, you would obviously want to get that value and use it in your programming (typically when the OK button has been clicked).
The code is essentially the reverse of what we have seen above. Note that since VBA only has the Date data type (and not a separate Time data type), you would declare your variable which is to hold the date picker’s user-set value as a Date Variable. Then simply set it to the date picker’s value:
Dim Date1 As Date Dim Time1 As Date […] Date1 = DTPicker1.Value 'Of course, the '.Value' is optional, since value is the default property. Time1 = TMPicker1.Value 'Of course, the '.Value' is optional, since value is the default property.
Again, you can make more certain with
Date1 = DateValue(DTPicker1) Time1 = TimeValue(TMPicker1)
But that does beg the philosophical question of how more certain you can be when you are already certain….
[And I think that’s my cue that this blog post has now gone on long enough!]