Make Pop Up Calender to the date (Excel 2007)

Use the Calendar control to fill in dates

How do I insert a Calendar control in my worksheet?

Excel 97-2003
Use Insert-Object on the Worksheet Menu Bar.
Select the control in the list and press OK.

Excel 2007-2010
On the Developer tab use Insert > ActiveX Controls….More controls.
Select the control in the list and press OK
Click on a cell on your worksheet.
Press the "Design Mode" button next to the "Insert button" to turn of Design Mode.

To display the Developer tab in Excel 2007 go to Office Button >Excel Options...Popular
In Excel 2010 : File>Options..Customize Ribbon, check Developer in the Main Tabs list on the right.

Excel 97-2010
It is possible you don't see it in the list, because it is installed with Access.
So if you don't have that program installed you possible don't have the control.
Note: The control is removed from Office 2010 so you must register the 2007 version.
You can download the control if you don't have it (See link on the bottom of this page).





Note 1: If you protect your sheet in Excel 97-2000 then you must format the range first with
the Date format you want and remove this line ActiveCell.NumberFormat = "mm/dd/yyyy"
In Excel 2002 and up you be able to protect your worksheet and allow Format cells.

Note 2: This code is not working if there are Merged cells in the range, but you can use this:
Excel 97-2003: Format>Cells>Alignment Tab ... Center Across Selection
Excel 2007-2010: In the Cells group on the Home tab use :
Format>Format Cells>Alignment Tab ... Center Across Selection


Where do I copy the code?

You must copy the code in the Worksheet module.
Right click on the sheet tab and choose view code.
Paste the code in the sheet module that is active now and press Alt-Q to go back to Excel.

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub

If you select a cell in the Range A1:A20 the Calendar will popup and when
you Click on the calendar the date will be placed in the active cell.
If you select a cell outside the range the Calendar will disappear.

Note: You can use this if your range is not one area
If Not Application.Intersect(Range("A1:A20,C1,E1"), Target) Is Nothing Then


Tip 1: if the cell value is a Date and you want that the Calendar popup with that date selected
you can replace this line Calendar1.Value = Date for
If Not IsDate(Target.Value) Then
Calendar1.Value = Date
Else
Calendar1.Value = Target.Value
End If
Tip 2: If you want that the calendar disappear when you click on a date in the control you have two options.

After ActiveCell.Select in the Calendar_Click event add this line.
Calendar1.Visible = False

Or select a cell next to the date cell, Replace ActiveCell.Select for:
ActiveCell.Offset(0, 1).Select

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