Excel Date Picker is a simple Excel add-in that allows to quickly insert a date in a cell from a calendar pop up where you can easily navigate to pick the date. Excel Date Picker adds an item to the cell drop-down menu that appears when right-clicking the cell, which takes you to the calendar date picker. As simple and fast as that!
Userform Structure
Excel Date Picker is built on a regular Userform with a number of boxes and buttons that allow to select the year, month, and day of the month. The year can be entered or updated in a text box, either manually or using the vertical spin buttons. The month can be selected from a combo box drop-down, or updated using the horizontal spin buttons situated on the left and right corners to move a month backward or forward respectively.
The Userform_Initialize event populates the combo-box with the list of the twelve months of the year, and displays the current month and year when date picker pops up. Here’s how we populate the options for a combo-box that show up in the drop-down.
Although, we rather rename the combo-box to something meaningful and easier to follow (for example MonthsBox). In this particular case, Excel already has the list of months in a custom list (custom list number 4), and we do not need to write the names. Instead, we could simply populate the combo-box as follows.
MonthsBox.List = Application.GetCustomListContents(4)
When the date picker appears, and also whenever the month or year are updated, the calendar for that month and year shows up. The week day headers and days of the months are buttons inserted in a frame within the Userform. Simple labels can also be used to display the days of the month, that’s actually the case for most of the other date pickers add-ins out there.
In order to show the calendar for the current month and year, we need to get the number of days and week day of the first day for that month and year. We do that with the following functions and formulas.
*Note that todayMonth is the number of the month, while the name of the month as a string could be taken from MonthName(todayMonth).
There are several ways to add the buttons or labels to display the days. The easiest way is to add them manually. That should cover six rows with the seven week days each. The value of each label or button is added later during macro execution depending on the selected month and year. Another way is to add them programmatically. This is a bit more advanced, if you are interested to know more about that let me know below in the comments and I will put some other article together.
Cell Drop-Down Menu
Excel Date Picker launches upon following the date picker entry in the cell drop-down menu. That date picker entry or item is pushed to the menu when the add-in has been added to Excel.
The add-in contains several event procedures that trigger that process. These include the Workbook_Open procedure, and also the Workbook_AddInInstall and Workbook_AddInUninstall procedures. Here’s the code to add an item to the cell drop-down menu.
The code first deletes any previously added menu item in order to avoid duplicates of the same entry. Deleting the control also happens when uninstalling the add-in. The item or command control added will call the “ShowDatePicker” macro, which displays the date picker Userform shown earlier. Picking a date will add the chosen date to that particular cell, as per the default date format or the custom format applied to the cell.
Create the Add-In
Excel Date Picker is a VBA project saved as an Excel add-in. We can easily turn any VBA project and macro-enabled Excel file into an add-in by simply saving the file with an .xlam extension. However, it is better to change the properties of the Workbook object module (ThisWorkbook) and deploy the VBA project as an add-in. At that point, the file can only be saved via VBA code input in the Immediate Window for example.
Additionally, the VBA project can be locked with a password to avoid users altering the structure or modifying the code, which could cause errors and malfunction of the date picker in Excel.
How to use the Add-in
As explained earlier, Excel Date Picker is very easy to use. We just need to download the add-in (follow the link below), save it to your preferred folder, and then go to the developer tab -> Excel Add-Ins -> Browse (and locate the file) -> add the file and tick the box. If you don’t have the developer tab, you can easily add it going to File -> Options -> Customize Ribbon -> and tick the box for Developer on the right hand side of the dialog window. If you do not want to add the developer tab, you can still add the add-in through File -> Options -> Add-Ins -> Manage Excel Add-Ins (at the bottom of the dialog) -> Browse (and locate the file) -> add the file and tick the box.
Once installed, you can simply right-click any cell to access the date picker entry that brings you to the calendar to pick a date that will automatically be added to the cell.
Additionally, you can incorporate Excel Date Picker in your macros and projects (if the add-in was previously added and enabled). For that, you simply add the reference in the VBA editor under Tools -> References -> DatePicker, and then you can launch the date picker calendar form with the line of code below.
Call DatePicker.ShowDatePicker
Download Excel Date Picker following the link below and feel free to share any feedback in the comments section. Let me know if it works well and you find it useful.
Click Here to download the Excel Date Picker Add-In.
No comments:
Post a Comment