Drop Down Calendar In Excel 2011

Posted on by

So you want to create a drop down calendar in Excel? Apart from sending alerts to Outlook from Excel (working on that article as well) this has been the most requested how-to as of late. For example:

The drop-down list is a great way to seem like a superuser and impress your co-workers and boss 🙂. At the same time, it’s a very user-friendly asset in almost all custom-made Excel sheets. In this tutorial, I’m going to show you: The 5 steps to create a drop-down in 1 minute or less. I call it the “1 Minute Drop-Down”.

  1. For certain versions of Excel, such as Excel 2011 for Mac, you'll need to select 'New from Template' from the File menu instead of 'New.' Creating a calendar from a template will allow you to create a blank calendar that you can fill in with events. It will not convert any of your data into calendar format.
  2. How do I insert a date calendar drop down within a cell or multiple cells? This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread.
  3. I've been directed here by the Greek Microsoft Answers Community about a problem that I'm facing in Excel 2016. The problem is that I want to insert a dropdown calendar in a cell.
  4. Insert Date Popup box in Microsoft Excel.Facebook Page: on twitter:: http:/.

Reader Tim Asks:

How do I insert a calendar as a way for a user to click on the cell have the date box open and choose the date that should be in the cell? Also where do I send the beer?

Reader Wayne Asked:

I am sure that I saw a pull down menu in a worksheet cell. I want to have a cell formated so that I can have a pull down menu of a calendar in which to choose the date.

This seems like it would be so easy yet when you look around on-line the answers are all full of VBA scripts and custom code.

Drop Down Calendar In Excel 2011

I assume you just want someone using the spreadsheet to be able to select a cell, have a calendar drop down and then populate the field with whatever date the person chooses. Yes it can be done. Yes it is fairly involved. And yes, I have a workaround.

Would you like the easiest, cheesiest, sleaziest way to do this? Well then watch this:

The first thing you need to do is enable the Developer Tab in Excel.

  1. Select the Pearl or “Office Button” in Excel. The Pearl is the ball in the top left corner that gives you the option to Print, Save, etc.
  2. With the Pearl menu open select Excel Options from the very bottom on the right.
  3. Another menu will open – Select Popular
  4. Check the box for “Show Developer Tab in the Ribbon“

Now you have the developer tab with all the controls for forms, xml, code, etc.

Go to the developer tab and select Insert, followed by the little Pink Floyd-esque More Controls icon.

Scroll through the list of controls and look what’s available. There are all sorts of funky add-ins you can use in your spreadsheets! We are looking for the Microsoft Date and Time Picker Control

Highlight it and select OK.

Your mouse will turn in to a cross hair – click and hold, draw a rectangle, release. You now have a date box!

You will also see code in the top akin to “=EMBED(“MSComCtl2.DTPicker.2″,””)”

You will also notice that you are in Design Mode because that icon is highlighted. This allows you to play with the settings for the date box you just created.

Mac

Deselect Design Mode and you have a date control! Select the drop down and a calendar appears! Holy Cow that’s super cool awesome but guess what! It doesn’t do anything!

You can click it and change the date as much as you want but nothing else is changing in your spreadsheet!

Here is where the genius of my non-code writing laziness pays off. Ready?

What cell do you want the date to change in? Let’s say C5

  1. Ok, In B5 type SELECT DATE:
  2. In D5 enter the formula =C5
  3. Still got that calendar control visible like in the image above?
  4. Let’s go back to Design Mode by clicking it. Move the calendar control box up around the C3 area just to move it out of the way.
  5. Right Click in the calendar control and select Properties.
  6. A properties menu will open up
  7. Look for the field Linked Cell and enter C5 (that’s where we want the date change to happen)
  8. You might get an error message – ignore it!
  9. Close the Properties menu
  10. Deselect Design Mode

Here’s where it all comes together. .

  1. Select the Date Drop down and change the date
  2. You will see Cell C5 change to that date
  3. You will see cell D5 (which =C5) change as well. The D5 was just to ensure that C5 was actually showing a date.
  4. Repeat step 1-3 until you are as giddy as a little girl.

Last Question: How do I use this?

Well you can create as many of these drop downs as you like. You can tell each one what cell to update (Linked Cell) and you can move the Date Control right over the top of the field it is updating! Think about that – Move the date picker right over the top of the cell it is linked to. Only you know the truth! There is no advanced code, you’re just hiding the data in C5 under the Date control that is updating or controlling C5! Then do any date calculations you need to do using C5 as the reference!

I told you it was sleazy, cheesy and easy!

I’ll make it even easier – here’s the spreadsheet I used to write this tutorial and take screen shots

Excel Drop Down Date Example (12k)

And Tim, You any any other reader can always send the beer to me by clicking beer mug at the top of the page.

CategoriesUncategorized

TagsCalendarDateDrop DownExcelPull Down

Related

-->

Note

Drop Down Calendar In Excel 2011

Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.

Summary

This article contains a sample Microsoft Visual Basic for Applications macro (sub-procedure) that prompts you for the month and year and creates a monthly calendar by using a worksheet.

Resolution

Note

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Drop Down Calendar In Excel 2011

To create the calendar, follow these steps.

Microsoft Excel 2003

  1. Create a workbook.
  2. On the Tools menu, point to Macro, and then select Visual Basic Editor.
  3. On the Insert menu, select Module.
  4. Paste the Visual Basic for Applications script from the 'Sample Visual Basic procedure' section into the module sheet.
  5. On the File menu, select Close and Return to Microsoft Excel.
  6. Select the Sheet1 tab.
  7. On the Tools menu, point to Macro, and then select Macros.
  8. Select CalendarMaker, and then select Run to create the calendar.

Microsoft Excel 2007 or later

  1. Create a workbook.
  2. On the Developer ribbon, select Visual Basic.
  3. On the *Insert menu, select Module.
  4. Paste the Visual Basic for Applications script from the 'Sample Visual Basic procedure' section into the module sheet.
  5. On the File menu, select Close and Return to Microsoft Excel.
  6. Select the Sheet1 tab.
  7. On the *Developer ribbon, click Macros.
  8. Select CalendarMaker, and then select *Run to create the calendar.

Note

If the Developer ribbon is not visible, open Excel Options to enable it. In Excel 2007, the option is available on the Popular menu. In Excel 2010, the option is available on the Customize Ribbon menu.

How To Insert A Drop Down Calendar In Excel For Mac

Sample Visual Basic procedure

You can add other code to customize the calendar to meet your needs. Insert extra rows for entry on the screen for each day or resize the screen to see the whole calendar based on screen size and resolution.