You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
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:
If you have any ideas or comments, or a date picker you love to use, please post them here!
Comments: (22) Collapse
Hi,
It's a really really well designed tool. I think the only problem is the way it's applied. I strongly agree with other reader that you should be able to apply it as a validation control. It's uses become limited when you need a date or the text 'date' in a sell above the desired cell. Is there any plan to extend the functionality to implement this. Or can you make your code open for extension by other people??
Again, brilliant control. I really like the UI. thanks
It really a great tool.
I am using right to left user interface (Hebrew UI)and if I click on the toolbar button, the calender is cut on the right side of the screen so I can't pick the year from the combo box or to click of the option button.
thanks,
Hanan
Sam: I have the same experience, clicking the link somehow convinces IE that it has an xls file to save, not an xla. On my site that kind of trouble does not exist because I tend to create zip files for download.
Hello,
I have a few questions.
1)How did you put the icon next to the date? Did you use vba? Is this icon a shape? Could you give a code example?
2)How did you create the date picker? Is it also a shape, or is it maybe a modified userform? Or maybe some activeX-component? Could you give a code example?
3)Could you post the source code (or parts of the source code). The icon and the date picker are very nice looking and useful features. It is really an extra to the user interface (data validation, userforms) of Excel.
Johan zendjohan AT hotmail DOT com
dbowe - Yeah more detection on when to show the icon in the grid and integration with data validation stuff would be sweet. Just to note: in the scenario “above the cell”, you can still right click the cell and put the date in :-) I’ll work on sharing out the code.
Hanan - Thanks! I didn't test international scenarios at all on the control, looks like a bug :)
Jan Karel Pieterse - Strange, I'm wondering if the XLA -> XLS thing is an IE issue or a website issue. Either way putting it in a zip is probably the way to go.
Johan De Schutter -
1) What is the icon - the icon is a shape with a picture in it. On load, if I remember correctly, I dump out an image to the temp directory that is the picture of the calendar, then use it on the shape.
2) What is the date picker - It’s a VBA user form with labels, combo boxes, and images. If I remember correctly (again), I use some API’s to hide the title bar and fade in and out the user form. Each day is a label and the background of the form is an image.
3)Share the code – I’ll get on it and take a pass through my backup hard drives, sounds like multiple folks would want to take a peek at it … or even better: enhance it. I’ll work to post it here or in another blog post.
That's a great add-in - i'd like to add to the call to see the source code: there's some really nice little touches in here that i could learn plenty from.
Thanks for posting the XLA though: it'll be enhancing my Excel experience right away!
Sam,
I JUST gave in an upgraded OP2K3 to OP2010. I do not have the path you mentioned to the XLA files so I am assuming you have an earlier version of Windows. I have Win7Pro x64. First question is, will the calendar control work with x64 versions of Windows? If so, will this work with OP2010 - I only saw mentin of Office 2007 in the article and replies. I really used the calendar control to death in Excel 2003 so I am hoping for good answers.