Hi Trickster,

Mike's code will do what you have requested as long as you have inserted
a calendar onto the sheet.

And if you do not have a Calendar control, see Ron De Bruin's Calendar page:

http://www.rondebruin.nl/calendar.htm

for a download link.

---
Regards,
Norman


"Rowan Drummond" wrote in message
...
Hi Rick

Mike's code will do what you have requested as long as you have inserted a
calendar onto the sheet. So step by step:

1.) In Excel select the sheet you want to apply the calendar to (any cell
for now).
2.) From the Menu's select InsertObject. This will bring up a dialog box
with a list of available Controls. Select "Calendar Control xx.x" where
xx.x is the version number. In my version of Excel 2002 this is 10.0 but
it shouldn't make a difference if you have a newer version with Excel
2003.
3.) You should now be able to see a calendar on the sheet. You may also
have an small floating toolbar with one button (Design Mode). You can
close this toolbar by clicking on the x.
4.) Right click the sheet tab and select View Code.
5.) Paste Mike's code onto the Code Module that is displayed.
6.) On the 6th line of code adjust the range to be the 1000 cells that you
would like to have the calendar linked to - Mike has assumed J1:J1000.
7.) Press Alt+Q to return to excel
8.) Select any cell OUTSIDE of the range you have specified. The calendar
should dissapear.
9.) Select any cell INSIDE the range you have specified. The calendar will
pop-up. Doubleclick on a date in the calendar and that date will appear in
the cell you selected. The calendar will remain visible until you click on
a cell outside of the range specified.

If I was implementing this for myself I would change the functionality
slightly so that only one click on the calendar is required and once a
date is selected the calendar is hidden - personal preference. To do this
right click the sheet tab again and select view code.

Replace these lines:

Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1.Value
End Sub

With:

Private Sub Calendar1_Click()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1.Value
Calendar1.Visible = False
End Sub

Press Alt+Q again to return to excel.

Hope this helps
Rowan