Sam Radakovitz on date pickers
Today’s author: Sam Radakovitz, a program manager on the Excel team that enjoys creating VBA applications in Excel.
Back in early 2005, Excel 2007 was under development, the Backstreet Boys released a new album, and I took a weekend out to create a date picker add-in for Excel. The add-in was fun to write, not super duper fancy code, but it got the job done and helped me learn about the various Excel events and Excel’s add-in model. I pushed it up on the web, got some feedback and made a couple of iterations on it, and by the end of the year I had wrapped it up and moved on to other things.
Recently this add-in got demo’d at a Microsoft event and it made it back on my radar. I decided to write a blog post about the add-in since some folks might find it useful … or at least spark some conversation about date pickers in Excel. If you want to try the add-in for yourself, you can download it and the sample file I’m going to use for this blog post here:
After downloading the add-in, you can stick it in your XLSTART folder (mine is at C:\Program Files\Microsoft Office\OFFICE12\XLSTART) and restart Excel for it to load.
The sample file contains a small list of projects for a department store. I’ve made the list small and removed some columns of information to keep things simple. With the date picker add-in installed, choosing a date in a cell will put an icon next to the date:
This is the entry point we will use to view the date picker, there are other entries points but I’ll get into them a bit later in the post. Clicking on the date picker icon will display the calendar:
The calendar fades in and defaults to the date that is in the selected cell. The green highlighted day represents where your mouse is. When you move the mouse to a different day, you’ll notice a few things change:
The green highlight moves with the mouse. The blue highlighted date, in this case January 1st, represents the selected cell’s value. As the mouse moves, the status text at the bottom tells you the date difference from the selected cell’s value. In this case we’re hovering over January 15th, so the status text is saying we’re going to move the date forward 14 days if we click on it. You can change how the status text displays the difference by clicking on the text:
Now instead of 14 days it says 2 weeks. Clicking on it will cycle through a few different groupings. To change the month displayed or the year, you can pick from the combo box at the top of the calendar or change the months by clicking on the left or right edge of the calendar:
Clicking the options button in the upper right of the calendar will fly out the options pane. Here you can specify the entry points you want visible and turn on and off the fading of the date picker:
Choosing the “In Cell Icon Settings…” button will display a popup of the different icons for the date picker control and give you additional options on where you want the icon position in the cell:
The cell icon will display when a date is selected, but there are other times it will display too. Below is a quick list of the entry points to the date picker:
The last thing I want to mention about the date picker is the warning text. If you have a formula (or other various things) populating the cell, picking a date will overwrite it. To help inform folks that the date picker is going to blow away the formula for the cell it will display some warning text on it:
And that’s about everything this date picker can do in a nut shell. There are some enhancements I’d like to make, not sure if I’ll get to them though:
- Update it for Excel 2007 & create nice ribbon buttons
- Support for setting time, like 2:30pm
- If a cell has a formula of =A1, and A1 is just a date, then I’d like to set A1 instead of blowing away the formula that refers to A1
- More ‘semi-smart’ detection for when to show the icon in the cell based on the data or objects around it
- Multiple visible months (previous and next to the left and right of the current month)
- Ability to limit the days available to choose from based on a cell reference
- Make a managed code version of it, right now its VBA and User Forms
- International support
If you have any ideas or comments, or a date picker you love to use, please post them here!