Software

How to acquire, position, and hide a Calendar control in an Excel sheet

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.
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:

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:

  1. Display the Control Toolbox toolbar and click Design Mode.
  2. Double-click the control to launch the sheet's module.
  3. 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

  1. Return to Excel.
  2. 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
This subprocedure combines the anchoring code from the last section with an IF that checks for a date value. There are many conditions you can use, but I think hiding the control when the active cell doesn't contain a date value is the most practical.
What innovative solutions have you used with the Calendar control?

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
j-fly
j-fly

Hi I only want the calendar control to be used with one specific cell. Can i anchor it to that cell and only have it visible when i click in that cell Thanks Jeremy

ETJ
ETJ

How about if you select a specific column (because you didn't put a date in yet but you want to, using the calendar control). E.g. Column A is the date column so if the active cell is in the "A:A" range show the calendar. If not, hide the calendar.

ssharkins
ssharkins

Checking for an existing date is really easy. What you're doing takes a bit more work -- but realistically, you're right -- the selected cell might be blank. Check out IsEmpty(), but do plenty of testing, but am empty cell isn't the same thing as a blank one.

ETJ
ETJ

Like this... If Left(ActiveCell.Address, 2) = "$A" 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

Editor's Picks