Search Contextures Sites

Contextures
Excel news
by email

 

 

Learn how to create Excel dashboards.

 

30 Excel Functions in 30 Days

 

Learn how to create Excel dashboards.

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

Time-saving
Pivot Table add-in

 

Excel Date Picker

Thanks to Jim Cone for contributing this date picker example.

Date Picker Overview

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.

date picker 01

Using the Date Picker

To use the date picker in the sample file, follow these steps:

  1. Open the workbook and press the 'Get a Date' button on the "This One" worksheet.
  2. date picker worksheet button

  3. The date picker form appears, and the current date is displayed on the form title bar (in the local date format).
  4. date picker title bar

  5. The two nested scroll bars at the top of the form are used to change the month and year.
    1. Click the arrows to move back or forward by one month
    2. Click the black boxes to move back or forward by one year
    3. NOTE: Changing the month or year removes the day selection

      date picker scrollbars

  6. Select a cell on the worksheet.
  7. Select a day in the date picker, and click the Insert button
  8. date picker insert

Date Picker Buttons

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.

Reset Button

  • Click -- Returns the calendar to the current date.
  • Shift + Click -- Adds a month calendar to the worksheet
  • date picker calendar month

  • Ctrl + Click -- Adds an entire year calendar to the worksheet

    date picker calendar year

Info / Undo Button

  • Info - Click -- Displays the file path of the "Get Your Own Date" workbook.
  • date picker info

  • Changes to an Undo button after a date is inserted.
  • Undo - Click -- Will undo up to twenty date insertions -- one at a time.

    date picker undo

Insert Button

  • Click -- Adds the selected date to the active cell
  • date picker insert

  • Shift + Click -- appends the selected date to the active cell
  • date picker insert

  • Additional cells can be selected and dates inserted.

Exit button

  • Closes the form.

    date picker insert

Date Picker VBA Code

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).

Download the Date Picker

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

Related Excel VBA Tutorials

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: May 22, 2013 7:45 PM