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?

Yes, you can easily insert a dropdown calendar into a cell in Excel 2007. Here are the steps:

1. Click on the cell where you want to insert the dropdown calendar.
2. Go to the "Developer" tab in the ribbon (if you don't see this tab, you may need to enable it in Excel options).
3. Click on the "Insert" dropdown in the "Controls" group, and select "More Controls".
4. In the "More Controls" dialog box, scroll down and select "Microsoft Date and Time Picker Control 6.0" (or a later version if available).
5. Click "OK" to close the dialog box.
6. Your cursor will now turn into a crosshair. Click and drag to draw the calendar control in your cell.
7. Right-click on the control and select "Properties".
8. In the "Properties" window, you can customize the appearance and behavior of the calendar control. For example, you can set the default date, the format of the date, and whether the user can type in the date or must select it from the calendar.
9. Close the "Properties" window and save your spreadsheet.

Now, when the user clicks on the cell with the calendar control, a dropdown calendar will appear. They can select a date from the calendar and it will be inserted into the cell.

hi
you might find some ideas here...
http://www.rondebruin.nl/calendar.htm

regards
FSt1

"Jeff Kass, San Diego, CA" wrote:

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?

not a good idea to broadcast who you are and where you are.

Tyro
"Jeff Kass, San Diego, CA" <Jeff Kass, San Diego,
wrote in message
...
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?

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

Tyro

"Jeff Kass, San Diego, CA" <Jeff Kass, San Diego,
wrote in message
...
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?

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 !!!

Bruce

You're right. There isn't an Office button in 2003. It was introduced at
2007.

And in reply to the second part of your message, to use an add-in you do
need to have the add-in installed and enabled.

It's always wise to quote in your message the relevant parts of the message
to which you are replying, as otherwise (for example) we don't know what you
mean by "step one".

For the benefit of anyone else who is trying to decipher what you may have
meant, looking at the headers of your message I see that the message to
which you replied, but from which you snipped the entire content, was
, which reads as follows:

"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

Tyro

"Jeff Kass, San Diego, CA" <Jeff Kass, San Diego,
wrote in message
...
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? "
--
David Biddulph

<Bruce Altenburger wrote in message
...
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 !!!

Bruce

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"
ActiveCell.Select
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





On Thursday, March 06, 2008 6:00 PM Jeff Kass, San Diego, C wrote:

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?


On Thursday, March 06, 2008 6:13 PM FSt wrote:

hi
you might find some ideas here...
http://www.rondebruin.nl/calendar.htm

regards
FSt1

"Jeff Kass, San Diego, CA" wrote:


On Thursday, March 06, 2008 10:31 PM Tyro wrote:

not a good idea to broadcast who you are and where you are.

Tyro
"Jeff Kass, San Diego, CA" <Jeff Kass, San Diego,


On Monday, March 17, 2008 1:17 PM Tyro wrote:

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

Tyro

"Jeff Kass, San Diego, CA" <Jeff Kass, San Diego,
wrote in message
...


On Wednesday, September 24, 2008 2:08 PM Bruce Altenburger wrote:

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 !!!



Bruce

Hello,

I've read through this thread as I to am trying to create a dropdown calendar option to a cell as the original poster. I've followed through the directions, and have a few additional questions. First, when I copy and past the VBA code for the Calendar Control 12.0, I changed the range of cells but it for some reason would not allow me to use a cell that is merged, is there a way to by pass this?