+ Reply to Thread
Results 1 to 10 of 10

Dropdown calendar in Excel

  1. #1
    Registered User
    Join Date
    12-12-2017
    Location
    Qatar
    MS-Off Ver
    10
    Posts
    24

    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.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-10-2017
    Location
    INDIA
    MS-Off Ver
    365
    Posts
    184

    Re: Dropdown calendar in Excel

    Hi,

    Follow the link below and add the addin to you excel:
    https://www.rondebruin.nl/win/winfil...DatePicker.zip
    *If you wish you click on *,a way to say ThankYou

  3. #3
    Forum Contributor
    Join Date
    09-08-2018
    Location
    Delhi
    MS-Off Ver
    2010
    Posts
    100

    Re: Dropdown calendar in Excel

    Quote Originally Posted by mathematix View Post
    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.
    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.
    Shashank

  4. #4
    Registered User
    Join Date
    12-12-2017
    Location
    Qatar
    MS-Off Ver
    10
    Posts
    24

    Re: Dropdown calendar in Excel

    Quote Originally Posted by Sunny18pc View Post
    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

  5. #5
    Registered User
    Join Date
    12-12-2017
    Location
    Qatar
    MS-Off Ver
    10
    Posts
    24

    Re: Dropdown calendar in Excel

    Quote Originally Posted by shank_mis View Post
    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).
    Attached Images Attached Images

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,338

    Re: Dropdown calendar in Excel

    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.
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  7. #7
    Registered User
    Join Date
    12-12-2017
    Location
    Qatar
    MS-Off Ver
    10
    Posts
    24

    Re: Dropdown calendar in Excel

    Quote Originally Posted by Richard Buttrey View Post
    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?

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,338

    Re: Dropdown calendar in Excel

    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.
    Last edited by Richard Buttrey; 01-21-2020 at 04:14 PM.

  9. #9
    Registered User
    Join Date
    12-12-2017
    Location
    Qatar
    MS-Off Ver
    10
    Posts
    24

    Re: Dropdown calendar in Excel

    Quote Originally Posted by Richard Buttrey View Post
    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.

  10. #10
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    1,860

    Re: Dropdown calendar in Excel

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dropdown calendar
    By bananacitizen in forum Excel General
    Replies: 4
    Last Post: 04-17-2018, 10:44 AM
  2. Can't change year range from dropdown menu in Excel Calendar
    By ConceivablyPerfect in forum Excel General
    Replies: 3
    Last Post: 11-23-2015, 07:37 PM
  3. Calendar dropdown box in Excel 2010
    By Pami in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-01-2015, 08:32 AM
  4. Need calendar dropdown or pop up box for a column
    By uglymaggot in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-21-2014, 11:11 AM
  5. DropDown Calendar
    By contaminated in forum Excel General
    Replies: 16
    Last Post: 05-22-2014, 01:57 AM
  6. Calendar Dropdown
    By Amit Chugh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-07-2013, 08:17 PM
  7. Excel 2007 : Inserting a Calendar into a Dropdown
    By lsbelt in forum Excel General
    Replies: 2
    Last Post: 03-29-2011, 03:26 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1