Cut down on errors by automating an Excel calendar control

Don't let mistakes corrupt your Excel data. Embed a Calendar control and eliminate typos in date values.

Anyone entering dates in Excel should receive hazard pay. Even the best designed spreadsheet can confuse a user and frankly, it's difficult to enter dates manually. Using the Calendar control can help. Instead of entering dates, you just click a date on a calendar! It couldn't be simpler. You might have seen this control in a user form, but you might not realize that you can embed one right into a sheet and use it in the same way. Customizing this control can be a complex process, but getting started is simple. First, you embed the control into a sheet. Then, you add the VBA code necessary to run the control. You embed and automate the control as follows:

  1. Display the Control Toolbox toolbar. To do so, right-click the background of any toolbar or menu and check Control Toolbox from the resulting list.
  2. On the Control Toolbox toolbar, click the More button. That's the hammer and wrench icon at the bottom.
  3. From the resulting list of additional controls, choose Calendar Control. (Your version number may differ.)
  4. Click in the sheet where you want the Calendar control to appear. A simple click is all that's required. You don't have to drag the mouse to size the control. However, you can resize the control later. Notice that the Formula bar identifies the Calendar control (when the control is selected). At this point, you can right-click the Calendar control and change a number of properties, but we won't explore those possibilities right now. Don't close the Control Toolbar yet -- it needs to remain open while you're designing the control.
  5. At this point, you're ready to add the VBA code that will run the calendar. Double-click the embedded control to launch the sheet's module in the Visual Basic Editor (VBE). In the Properties window, you can see that Excel named the embedded control Calendar1.
  6. Enter the following code in the sheet's module: Private Sub Calendar1_Click() 'Enter clicked date into selected cell. ActiveCell.Value = Calendar1.Value

    End Sub

  7. Return to the Excel sheet.
  8. Click Exit Design Mode on the Control Toolbox toolbar (that's the first icon). Excel won't execute anything in the Calendar control until you exit Design mode. Later, if you want to change the control, display the Control Toolbar and click Design Mode.
If you're using Excel 2007, the process is similar, but accessing the control is different. You'll need to do the following:

  1. Click Insert in the Controls group on the Developer tab. Then, click More Controls.
  2. Follow steps 3 through 7 above.
  3. To exit Design mode in Excel 2007, click Design Mode on the Developer tab in the Controls group.
The Calendar control is ready to use. For example, to enter a date into the empty cell, I7 (using the spreadsheet shown above) you'd select I7. Then, click any date in the embedded Calendar control. Clicking a date will execute the control's Click event, which will enter the clicked date into the selected cell. With the Calendar control and one line of code you have eliminated data entry typos! Two things are worth noting:
  • If the Calendar control isn't available (in step 3), you may need to install or register it. This seldom happens, but if it happens to you, review the article Add or Register an ActiveX Control. The process is similar for Excel 2003, but you'll access the commands via the Control Toolbox toolbar instead of the Developer tab.
  • If the Developer tab in Excel 2007 isn't visible, you can display it quickly enough: Click the Office button; click Excel Options; select Show Developer Tab In The Ribbon (in the Top Options For Working On The Ribbon section); and click OK.
Do you have an Excel workbook that uses a Calendar control? If so, tell us about it. Or have you tried to use this control and failed? If that's the case, share your experience here. Maybe someone will have a solution for you.