Don't let mistakes corrupt your Excel data. Embed a Calendar control and eliminate typos in date values.
- 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.
- On the Control Toolbox toolbar, click the More button. That's the hammer and wrench icon at the bottom.
- From the resulting list of additional controls, choose Calendar Control. (Your version number may differ.)
- 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.
- 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.
- Enter the following code in the sheet's module:
Private Sub Calendar1_Click() 'Enter clicked date into selected cell. ActiveCell.Value = Calendar1.Value
- Return to the Excel sheet.
- 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.
- Click Insert in the Controls group on the Developer tab. Then, click More Controls.
- Follow steps 3 through 7 above.
- To exit Design mode in Excel 2007, click Design Mode on the Developer tab in the Controls group.
- 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.