My recent post on embedding a Calendar control in an Excel sheet generated a lot of interesting questions. In this post, I’ll answer three of them:
- 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 control
The 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:
- Excel 2007: Add or register an ActiveX control
- Excel 2003: Add or register an ActiveX control
How to control the position of the Calendar control
When 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
End Sub
- Return to Excel.
- Click Exit Design Mode on the Control Toolbox toolbar.
Select any cell to trigger the sheet’s Selection Change event and see how the control reacts. The control will always be just a quick click away from the active cell. The example code adds a combination of values to position the control. In this case, I added 30 to the control’s top and left borders. Subtracting a value from the Top value helps to center the calendar vertically with the active cell. If you add a value, you’ll push the control down. You can adjust these value to position the control just where you want it. You might even choose not to offset the control at all and omit the additional value.
How to hide the Calendar control
The 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
With Calendar1
.Visible = True
.Left = ActiveCell.Left + ActiveCell.Width + 30
.Top = ActiveCell.Top - 30
End With
Else
Calendar1.Visible = False
End If
End Sub
