How to add a DatePicker (Calendar) to Excel Cells


How to add a DatePicker (Calendar) to Excel Cells 1

Do you need a DatePicker Calendar in your Excel spreadsheets? Follow this quick and simple tutorial to find out how. (If you prefer video, scroll down to the bottom of the page!)

Step 1. Enable Developer Mode in Excel

  • File
  • Options
  • Customize Ribbon
  • Ensure Developer checkbox is enabled
Enable Developer Mode

Step 2. Setup DateTime Control box

  • Microsoft Date and Time Picker Control
  • Click on random cell to place control box
  • Edit or remember the name box of the DateTime Picker, in this case, we will leave it as DatePicker1
Name Date Picker
  • Right click on DateTime Control box
  • DTPicker Object
  • Properties
DTPicker Object > Properties
  • Enable CheckBox
Enable Checkbox
Excel Training Guide

Step 3. Copy the Visual Basic code

To assign column A as a DatePicker, copy the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
  With Sheet1.DTPicker1
    .Height = 20         
    .Width = 20         
    If Not Intersect(Target, Range("A:A")) Is Nothing Then             
      .Visible = True             
      .Top = Target.Top             
      .Left = Target.Offset(0, 1).Left             
      .LinkedCell = Target.Address         
    Else             
      .Visible = False         
    End If     
  End With               
      
End Sub
  • Right click the DateTime Control box
  • View Code
View Code
  • Replace all of the existing code with the code from above
  • Close the Visual Basic editor
Paste Code
  • Deselect Design Mode
Deselect Design Mode
  • Click on any cell to remove DateTime Control box
  • Click on any cell in Column A to add a Date
Pick Date

Congratulations!

Important: Make sure that when you save the spreadsheet, you “Save As: Macro enabled workbook”

How to have multiple columns with DatePickers

The above steps only work for single columns, or columns that are right next to each other. If you wanted the DatePicker column to be in column B instead, then you would change the line

If Not Intersect(Target, Range("A:A")) Is Nothing Then

to

If Not Intersect(Target, Range("B:B")) Is Nothing Then

Or if you wanted it from Column E to Column G, then you would change that line to

If Not Intersect(Target, Range("E:G")) Is Nothing Then

However, if you need a DatePicker in two or more non-adjacent columns, then you will need a separate DatePicker (each with a separate name in the Name Box) for each non-adjacenet group. For example, let’s say we need column A, B, E, F, and H to all be DatePickers. We would need to perform the above steps 3 separate times for 3 separate DatePickers.

  • 1 for columns A, B
  • 1 for columns E, F
  • 1 for column H

The steps will be very similar, but the code will be:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    

  With Sheet1.DTPicker1    
    .Height = 20             
    .Width = 20             
    If Not Intersect(Target, Range("A:B")) Is Nothing Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.Address
    Else
      .Visible = False
    End If
  End With

  With Sheet1.DTPicker2    
    .Height = 20             
    .Width = 20             
    If Not Intersect(Target, Range("E:F")) Is Nothing Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.Address
    Else
      .Visible = False
    End If
  End With

  With Sheet1.DTPicker3    
    .Height = 20             
    .Width = 20             
    If Not Intersect(Target, Range("H:H")) Is Nothing Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.Address
    Else
      .Visible = False
    End If
  End With
  
End Sub

Important legal notice: This post contains affiliate links.  We are compensated for many of our product recommendations.  Pragmatic Ways is a participant in the Amazon Associates affiliate program, as well as other affiliate programs.  While we are always careful to only recommend the products we use and recommend, we want to be open and transparent about our relationship with companies we recommend.

Recent Content