Calendar Control to enter Dates in Excel Worksheet

How to use a calendar control in Excel to enter dates in a worksheet quickly and easily
The calendar in Excel is a useful object to make entry of dates in an Excel worksheet easier, quicker and more intuitive. You can use a date in a variety of situations: when were the goods delivered? When did an employee join the company? A calendar can be used to follow the progress of a project. You can use many functions like conditional formatting, IF, etc to create a calendar. The process is quite tedious. Inserting a calendar using the calendar control is quick and easy. It’s a click and drag process. The insertion of dates into an Excel worksheet cell by clicking on the calendar can be done using a one line macro. Even if you have no idea of programming our video demonstrates how you can do this easily. The different steps involved are given below for your ready reference:

  • Click on the developer tab on the Microsoft Office Ribbon
  • Click on ‘more controls
  • Select the calendar control. Click OK
  • Now click and drag to draw a calendar of a size of your choice. You can later format the size, the background, fonts and the borders using the ‘properties as shown in the training video
  • While in the design mode double click on the calendar object, the Visual Basic Editor opens up
  • Between the ‘sub’ and the ‘end sub’ you need to write one line of code which on execution transfers the date value from the calendar to the active cell or the cell which you have selected.

Private Sub Calendar1_Click()
ActiveCell.Value = Calendar1.Value
End Sub

Further reading:
Use a Calendar control or Date Picker to fill in dates