Hi,

Does anyone know a way to create a drop down calendar in a cell such
that when the user clicks on the cell a calendar appears allowing them
to select the required date. This date then appears in the cell.

Is this possible or beyond the capabilities of excel?

(NB I am using excel2000)

Many thanks for your consideration of this problem


---
Message posted from http://www.ExcelForum.com/

Try this Ali

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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"ali " wrote in message ...
Hi,

Does anyone know a way to create a drop down calendar in a cell such
that when the user clicks on the cell a calendar appears allowing them
to select the required date. This date then appears in the cell.

Is this possible or beyond the capabilities of excel?

(NB I am using excel2000)

Many thanks for your consideration of this problem


---
Message posted from http://www.ExcelForum.com/

Many thanks for your help, however it is not quite working as i
hoped...

I have followed the steps you gave me and now have a calendar on screen
permanently.

Ideally i want the calendar to be hidden until the user clicks on cell
"c3" and to then disappear once the user has selected the desired date.
The desired date will of course be left behind in cell c3.

Any idea what i'm doing wrong? Many thanks


---
Message posted from http://www.ExcelForum.com/

If you only want to use it in C3 then use this

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

' This will hide it after you DblClick on a date
Calendar1.Visible = False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' this is the range where the SelectionChange is working
' I change it to c3 only
If Not Application.Intersect(Range("c3"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
Else: Calendar1.Visible = False
End If
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"ali " wrote in message ...
Many thanks for your help, however it is not quite working as i
hoped...

I have followed the steps you gave me and now have a calendar on screen
permanently.

Ideally i want the calendar to be hidden until the user clicks on cell
"c3" and to then disappear once the user has selected the desired date.
The desired date will of course be left behind in cell c3.

Any idea what i'm doing wrong? Many thanks


---
Message posted from http://www.ExcelForum.com/

Thanks again, i'm almost there now but not quite...

If i may, this is what i am trying to create:

The sheet opens, a macro is run which creates a template (this is all
working fine)

The user then clicks on cell c3 which will bring up the calendar for
the first time (the user will not see the calendar until they click or
double click on c3)

The user then selects the desired date and the calendar disappears.
The date selected remains in cell c3.

I apologise if this should be obvious - i am not yet as competant an
excel user as i hope to become!!!

Your help is very much appreciated


---
Message posted from http://www.ExcelForum.com/

Thanks Ron,

I didn't know Excel had a Calendar Control that you could
add to a form. I was able to duplicate it all quite
easily, after you pointed out the way.

I found that the users may want to show the calendar form
another way by double-clicking the activecell, by using
this well-known programming code with two command buttons:

Sub ShowTheCalendarDClick()
ActiveSheet.OnDoubleClick = "ShowForm"
End Sub

Sub DontShowTheCalendarDClick()
ActiveSheet.OnDoubleClick = ""
End Sub

Sub ShowForm()
frmCalendar.Show
End Sub

-----Original Message-----
Try this Ali

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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"ali " wrote in
message ...
Hi,

Does anyone know a way to create a drop down calendar
in a cell such
that when the user clicks on the cell a calendar
appears allowing them
to select the required date. This date then appears in
the cell.

Is this possible or beyond the capabilities of excel?

(NB I am using excel2000)

Many thanks for your consideration of this problem


---
Message posted from http://www.ExcelForum.com/



.

The code is doing what you want??

If you select c3 it will display the calendar
If you double click on a date it will place this date in the cell and
the calendar disappear

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"ali " wrote in message ...
Thanks again, i'm almost there now but not quite...

If i may, this is what i am trying to create:

The sheet opens, a macro is run which creates a template (this is all
working fine)

The user then clicks on cell c3 which will bring up the calendar for
the first time (the user will not see the calendar until they click or
double click on c3)

The user then selects the desired date and the calendar disappears.
The date selected remains in cell c3.

I apologise if this should be obvious - i am not yet as competant an
excel user as i hope to become!!!

Your help is very much appreciated


---
Message posted from http://www.ExcelForum.com/

Hi rick

If you want it in a userform see this link(also on my site)
http://www.fontstuff.com/vba/vbatut07.htm

You can add the userfom/code to your personal.xls
this way you can use it in every workbook.

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Rick" wrote in message ...
Thanks Ron,

I didn't know Excel had a Calendar Control that you could
add to a form. I was able to duplicate it all quite
easily, after you pointed out the way.

I found that the users may want to show the calendar form
another way by double-clicking the activecell, by using
this well-known programming code with two command buttons:

Sub ShowTheCalendarDClick()
ActiveSheet.OnDoubleClick = "ShowForm"
End Sub

Sub DontShowTheCalendarDClick()
ActiveSheet.OnDoubleClick = ""
End Sub

Sub ShowForm()
frmCalendar.Show
End Sub

-----Original Message-----
Try this Ali

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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"ali " wrote in
message ...
Hi,

Does anyone know a way to create a drop down calendar
in a cell such
that when the user clicks on the cell a calendar
appears allowing them
to select the required date. This date then appears in
the cell.

Is this possible or beyond the capabilities of excel?

(NB I am using excel2000)

Many thanks for your consideration of this problem


---
Message posted from http://www.ExcelForum.com/



.