Dropdown calendar in Excel

I tried to search for this query in the forum but did not seem to find anything, hence posting it here. (Though sorry if it has already been posted and I failed to find it)

I need to insert a dropdown calendar within the excel worksheet. I found the add-in for Date Picker and was able to install it. However, the option only appears when I right click on the cell. Also, I think the option will only be available if the add-in is installed in the user's computer. As I will be sending the worksheet to different teams, I would like to avoid asking each of them to install the add-in as they might find it complicated.

What I am looking for is something that is straight-forward and similar to data validation wherein when you click on the cell, the calendar will appear and the user will be able to choose the date accordingly. I know that it would be easier to simply type the date but I am exploring an option where this is possible in order to make sure the accuracy of each date entry.

Thanks for your help.

Originally Posted by Sunny18pc

Hi,

Follow the link below and add the addin to you excel:
https://www.rondebruin.nl/win/winfil...DatePicker.zip

hi thanks, this is actually what I have. But it will only work if the user has the add in installed in their PCs

Originally Posted by shank_mis

Can be done with the drop down box (Data Validation). But here you have to dedicate two rows... One for Month and another for Date...for smooth navigation.
Data Validation with One Row will present a little difficulty at times of date selection as it will show you entire 12 month's date at once...

If you want to proceed with Data Validation with Month & Date, please let us know.

Thanks, I did try data validation prior to posting this but the data validation is restricted as to the dates that you will indicate. What I am looking for is something like what some websites have when choosing a date (see attached). A dropdown menu which will show the user the calendar to pick the date. By the way, I am using Excel 2013 (64-bit).

Originally Posted by Richard Buttrey

In typical Blue Peter style "here's one I prepared earlier"..(UK Reference ) which you may be able to incude in your system.

It's a workbook with a cell named 'StartDate' currently A1.
Whenever the cell is changed a date picker form is opened allowing you to pick a date from drop down boxes.

I thought I hit reply ealier :D This is awesome. Can you tell me how I can replicate the formula throughout the worksheet? Is it also possible to expand the choices?

Originally Posted by Richard Buttrey

There is no formula. If you want the date picker functionality in your production workbook then you'll need to go to the macros. One way is Alt-F11.

Open the workbook I sent and ALTF11. There on the left in the VBA Project pane for the Workbook file you'll see two objects.

DatePickForm
DatePickModule

Save each of them in turn to say your Desktop by using the 'File..Export File' menu in the VBE.

Then open your production workbook, select that workbook in the VBA project pane and then use the 'File..Import File' menu in the VBE to load the form and the macros necessary into your production workbook.

By 'expand the choices' what are you thinking of? This is a date picker and will result in a date being placed in the cell named 'StartDate'.
Do you mean you want to add other sorts of information? The answer is of course yes but you'll need some familiarity with Macros to add new boxes etc.

To run the macro on any other sheet you'll have to create the same range name 'DateStart' at a worksheet level using the Name Manager. Then add the same sheet selection change event code to the sheet in question in the VBE
i.e.

Please Login or Register  to view this content.

hi thanks a lot for the detailed explanation. i am kind of stuck in importing the file to load the form and macros necessary. i am not very familiar with macros. in terms of expanding the choices, i am looking at adding more years as to not limit it to 3 years.

Another one to try, at present will show in column (click A2 downwards) you can alter the code and use 'ActiveCell'
At present year choice (2017 - 2030) can be altered anything of your choice in the UserForm initialisation code.