One can use the following method to add Excel Date Picker / calender based entry in the excel sheet for the Excel versions 2007 and above.
1. Add the date picker userform from here and import it in VBA editor
2. Add a Class Module in VBA editor and rename it “ClsCalendar”. Paste the following code from here in this new class module
3. Add a module and paste this code in the module:
Public clsCal As ClsCalendar
4. You can now link the date picker in the VBA code of the worksheet for selection change event so that if user clicks on a cell in the worksheet, the date picker pops up. Here is the sample code you can use:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Not Intersect(Target, [DateRange]) Is Nothing Then
Dim myDate As Date
Set clsCal = New ClsCalendar
myDate = clsCal.SelectedDate
If myDate > 0 Then
Target.Value = clsCal.SelectedDate
Set clsCal = Nothing
Note: This code was found on internet, not sure about the source/author of the code.