Here are a few tricks for working with the Calendar control in Excel. Learn how to acquire the control if you don't have it, how to control where Excel displays it, and how to hide it when you don't need it.
- How to get a copy of the control
- How to control the position of the control
- How to hide the control
How to get a copy of the Calendar controlThe easiest and safest way for Office 2003 users to get this control is to download and install Office 2003 Service Pack 3. Be sure to install the file in the right folder: C:\Windows\System32. (Depending on your version of Windows, you might have a System folder instead of a System32 folder.) If you're using Excel 2007 but haven't installed Access, you might not see the control in the list of ActiveX objects. Simply install Access. It's not the best solution, but it's probably the safest. There are a number of sites that host the control, and they're most likely safe, but downloading is always a risk. If you still can't see the control, register it. You can find more information on registering an ActiveX control in the following articles at Microsoft.com:
How to control the position of the Calendar controlWhen you embed a control in a sheet, it stays where you put it. If your sheet is larger than a single screen — and most are — you'll quickly lose sight of, and easy access to, the control. The easiest way to handle this problem is to freeze the pane. Just be sure to embed the control above the part of the sheet you'll be working with. That way, it will always be visible. However, this solution is a bit awkward. First, if the sheet already exists, adding the necessary rows to accommodate the Calendar might have repercussions. Be sure to save a copy of the workbook before you start. Second, you still have to move from the active cell to the top of the sheet to click the Calendar. That's not a big deal, but it could get tiresome if you're entering a lot of dates this way. Using VBA, you can attach the control to the active cell. This way, the calendar's always right where you need it. But having the calendar move a little every time you select a cell can become an annoyance too. In the end, you'll just have to choose what works best for you. To anchor the control to the active cell, do the following:
- Display the Control Toolbox toolbar and click Design Mode.
- Double-click the control to launch the sheet's module.
- Enter the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Anchor control to the active cell.
Calendar1.Left = ActiveCell.Left + ActiveCell.Width + 30
Calendar1.Top = ActiveCell.Top – 30
- Return to Excel.
- Click Exit Design Mode on the Control Toolbox toolbar.
How to hide the Calendar controlThe Calendar control has a Visible property. By setting this property to False, you can hide the control. But choosing the right method for this can be tricky. For instance, you could set the Visible property to False via the control's Double Click event — but then, how would you unhide it? You'd have to remember exactly where the control was when you hid it. That's too hard. You could add a new command to a toolbar that hides and unhides the control. Perhaps the easiest way is to hide the control conditionally. For example, you might want to hide the control when the active cell doesn't contain a date — that makes the most sense. Then, you don't have to hide and unhide it at all; Excel does it all for you. Use the following subprocedure to hide the control, conditionally:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)'
Anchor control to the active cell.'
Display control only when active cell contains a date.
If IsDate(ActiveCell.Value) Then
.Visible = True
.Left = ActiveCell.Left + ActiveCell.Width + 30
.Top = ActiveCell.Top - 30
Calendar1.Visible = False