Drop down calendar function

[Guest]

I am creating a spreadsheet and would like to include a drop down menu with a
calendar format to avoid typing the date longhand. Can anyone suggest how
this is done ?

 

[Guest]

Hi Piers,

You can do this with a macro..

Right click on the icon nex to File in the menu bar at the top of the
screen, select the sheet where you want the calender from the options on the
left.

Paste this into there...

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "dd/mm/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("A1:A5"), 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


Change the range ("A1:A5") at this line to fit your requirements...

If Not Application.Intersect(Range("A1:A5"), Target) Is Nothing Then

Hope this helps,

Gav.

 

[Ron de Bruin]

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