Excel - How do I insert a dropdown calendar into a cell?

Asked By Jeff Kass, San Diego, C on 06-Mar-08 06:00 PM
I am setting up a spreadsheet as a form.  One of the cells will contain a
date the form was filled in. I would like to make it easy on the user by
making a small calendar popup when they click on the date cell.  Then, the
only need to click on a date in the calendar and it will insert it.  Airline
reservation sites do this a lot.

Anyone know how to do this in Excel 2007?

FSt replied on 06-Mar-08 06:13 PM
you might find some ideas here...

Tyro replied on 06-Mar-08 10:31 PM
not a good idea to broadcast who you are and where you are.

Tyro replied on 17-Mar-08 01:17 PM
Yes. Excel 2007.

1) Office Button/Excel Options/Popular/Top options for working with Excel -
Check box Show Developer tab in Ribbon
2) Click Developer Tab
3) In Controls group click Insert
4) In Active X controls click on the bottom rightmost icon - More controls
5) Select Calendar Control 12.0 and click OK
6) Draw a box on the spreadsheet about 10 rows high and 5 columns wide
7) Right click on the calendar and select Properties
8) Change LinkedCell (blank) to be the cell you want the date to be in (A1,
G6 for example)
9) Click Design Mode in Controls group to exit Design Mode and have the
calendar work
10) Click on a date in the calendar and you should see that date in the cell
you entered in step 8
11) To make further changes click Developer tab, Controls group, Design
Mode, Click the calendar and change whatever

Bruce Altenburger replied on 24-Sep-08 02:08 PM
I guess this doesn't work for Excel 2003?  I guess this sounds stupid but I can not find the "Office Button" in step one.  I am hoping it is b/c it doesn't exist with 2003 and that I am not being a total idiot.

I had used another method where you download the add-in from Ron de Bruin, and it works on the spreadsheet I created, however, when I sent it to someone else the calendar pulldown does NOT appear.  I presume this is b/c they'd have to download the add-in too?

Thanks for your help !!!

Ron Drew replied to Bruce Altenburger on 28-Jun-11 03:48 PM
Change display format and range to whatever you want...when you click on one of the cells in the range the pop-up will appear..If you hit the wrong cell by mistake just use the delete key...

Insert the following code:

Private Sub Calendar1_Click()

ActiveCell.Value = CDbl(Calendar1.Value)

ActiveCell.NumberFormat = "mm/dd/yyyy"


If Calendar1.Value Then

Calendar1.Visible = False

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Application.Intersect(Range("b4:l10"), Target) Is Nothing Then

Calendar1.Left = Target.Left + Target.Width

Calendar1.Top = Target.Top + Target.Height

Calendar1.Visible = True

Calendar1.Value = Date

ElseIf Calendar1.Visible Then Calendar1.Visible = False

End If

End Sub