Create a drop down calendar in a cell?

  • Thread starter Thread starter ali
  • Start date Start date
A

ali

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




"ali >" <<[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
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
 
Back
Top