Is it possibile to enter dates from a drop-down calendar?

Question

In Excel 2003, is it possible to enter dates in a cell from a drop-down calendar instead of entering them manually?

Answer 1

Is it possible to enter dates in a cell from a drop-down calendar?

Use the following for Excel 2003.

---

Adding a calendar drop down box to Excel 2003

1. Insert > Object, scroll down and click on "Microsoft Calendar Control".

2. Right click the worksheet tab and view code.

3. Paste the following code:

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
       If Target.Cells.Count > 1 Then Exit Sub
       'Change this to your range of dates
       If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
           Calendar1.Top = Target.Top + Target.Height
           Calendar1.Left = Target.Left + Target.Width / 2 - Calendar1.Width / 2
           Calendar1.Visible = True
           Calendar1.Value = Now
       ElseIf Calendar1.Visible Then Calendar1.Visible = False
       End If
   End Sub
   Private Sub Calendar1_Click()
       ActiveCell.Value = (Calendar1.Value)
       ActiveCell.NumberFormat = "dd mmm yy"
   End Sub
   

   Note in the code the range for the dates is set as A1:A100, maintain the syntax and change this to suit.

4. Close VB editor.

5. Exit design mode.

When you click in a1:A100 you get a calendar that defaults to today's date.

Click a date and it gets entered in the active cell.

Source Adding a calendar drop down box to Excel 2003

Answer 2

There is number of ways:

• adding form field with dropdown calendar can be more comfortable, but suitable for limited cell count made manually.

  1. enable Developer tab in Excel
  2. select Insert > others (bottom-right icon) > Microsoft Date and Time Picker Control 6.0
  3. place the control on your sheet
  4. using Developer tab, display property sheet of the control
  5. set LinkedCell property to your cell
  6. using Developer tab, switch off Design Mode

• adding universal date/time dropdown picker can be slightly less intuitive to use, but you do not need any further special handling of date cells

  • you can either get that addin (well, for excel-2003 you need to find something like that) or create your own with similar functionality (needs experience with software development). The way goes through Excel add-ins.