Search Contextures Sites
Thanks to Jim Cone for contributing this date picker example.
- Date Picker Overview
- Using the Date Picker
- Date Picker Buttons
- Date Picker VBA Code
- Download the Date Picker
- More Excel VBA Tutorials
The date picker -- Get Your Own Date -- is in a self-contained workbook with the VBA code unlocked. The form is similar to the date picker found on banking web sites, and contains a few extra features, such as creating calendars on the worksheet.
To use the date picker in the sample file, follow these steps:
- Open the workbook and press the 'Get a Date' button on the "This One" worksheet.
- The date picker form appears, and the current date is displayed on the form title bar (in the local date format).
- The two nested scroll bars at the top of the form are used to change the month and year.
- Click the arrows to move back or forward by one month
- Click the black boxes to move back or forward by one year
- NOTE: Changing the month or year removes the day selection
- Select a cell on the worksheet.
- Select a day in the date picker, and click the Insert button
There are four buttons at the bottom of the date picker, and each button has a tool tip that appears when you point to the button.
- Click -- Returns the calendar to the current date.
- Shift + Click -- Adds a month calendar to the worksheet
- Ctrl + Click -- Adds an entire year calendar to the worksheet
Info / Undo Button
- Info - Click -- Displays the file path of the "Get Your Own Date" workbook.
- Changes to an Undo button after a date is inserted.
- Undo - Click -- Will undo up to twenty date insertions -- one at a time.
- Click -- Adds the selected date to the active cell
- Shift + Click -- appends the selected date to the active cell
- Additional cells can be selected and dates inserted.
- Closes the form.
To see how the date picker works, you can examine the VBA code -- the code modules in the workbook are unlocked.
- The form uses 7 listboxes to display the days of the month.
- Using 64 bit Office may require changes to the code.
- The 'ResizeToRightSize' and 'RefinishTheForm' functions use the screen resolution setting to adjust the form size. (called from the UserForm_Initialize sub)
- The initial form position on the screen can vary depending on the active cell location. (code in the UserForm_Activate sub).
You can download the sample date picker file, which is in Excel 2003 format, and zipped. The Get Your Own Date file contains macros, so you will have to enable macros to test the date picker.
Copyright ©2013 Jim Cone
Contextures Inc., Copyright ©2013
All rights reserved.
Last updated: May 22, 2013 7:45 PM