Software

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.

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.

41 comments
CharlieGH
CharlieGH

this has been a wonderful resource. 

I need to put several such calendar controls in the same worksheet. I've been trying with no luck. It inserts another calendar but the original stops working. I reckon it must have something to do with the incorrectly labelling in the visual basic editor. Help would be gratefully appreciated.

Thanks in advance

kfong6932
kfong6932

HI ALL, Sorry, I am late for the "party". I am new in using VBA. Is that possible to install the " Calendar Control " using VBA code or macro ? Thanks.

sarith_r
sarith_r

Is this available in Excel 2010?

skiesler
skiesler

I was able to add the control and put in the code but when I returned to the worksheet the calendar just stays on the page and does not execute. I did this at home on my laptop in 2007 and it worked fine. I am not allowed to upgrade at work. Can you tell me how I can know if the code is executing? In 2007 I had to trust the site and install the active x control but I was not asked to at work. Thanks.

nrobin
nrobin

Can this be done in Excel 2003? thx

bunitt
bunitt

Can't click and drag around the spreadsheet?

jdc100
jdc100

The cal is visible at all times, how do I hide it until ready for use jdc100@sbcglobal.net

pgurney
pgurney

This looks like a really helpful hint, but I get a syntax error when I click the calendar to select the date. I'm using Excel 2007. I copied the VBA code from above. I exited design mode. When I click on the cell where I want the date and then the calendar date, I get the invlaid syntax error. What am I doing wrong?

gmikijanic
gmikijanic

Would be great if the panel containing the calendar could float - especially as the spreadsheet was rolled up and down on the monitor. When entering many dates in (eg.) a column, the current solution has the calendar scroll off the top of the screen along with the top rows of the spreadsheet.

malcolm
malcolm

Good one. But if you just want to enter todays date/time into a cell, properly formatted, create the following macro and assign a hotkey to it - I use CTRL-T - for Timestamp. Sub today() ' ' today Macro ' Inserts @now() result into current cell ' ' Keyboard Shortcut: Ctrl+t ' ActiveCell.FormulaR1C1 = Now() End Sub

abermudezsalazar
abermudezsalazar

Great, works jsut fine in my Excel 2003. Thanks, very helpful.

slavin
slavin

Excellent - great tip! using it for my To-Do / Project task list

JMF667
JMF667

Now I really understand why John Walkenshaw developed Power Utility Pack. PUP Version 7 has PUP Date Picker, works a treat. Don't think of using anything else.

rmc400
rmc400

sound a great tip. But Microsoft's download site seems not to offer an add-in for Excel 2003 or 2007 for calendar, and it's not included in the Office 2003 or 2007 that I have.

HAL 9000
HAL 9000

Try reposting this in the 'Q&A' forum. The 'Discussion' forum is for matters of general discussion, not specific problems in search of a solution. The 'Water Cooler' is for non-technical discussions. You can submit a question to 'Q&A' here: http://www.techrepublic.com/forum/questions/post?tag=mantle_skin;content There are TR members who specifically seek out problems in need of a solution. Although there is some overlap between the forums, you'll find more of those members in 'Q&A' than in 'Discussions' or 'Water Cooler'. Be sure to use the voting buttons to provide your feedback. Voting a '+' does not necessarily mean that a given response contained the complete solution to your problem, but that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours. If they have a ready source of reference available, perhaps won't need to repeat questions previously asked and answered. If a post did contain the solution to your problem, you can also close the question by marking the helpful post as "The Answer". [b]2 years[/b] is a little late for anyone to actually see this so I would suggest reposting as a Question.;) Col

bunitt
bunitt

Thanks everybody for all those tips

dkidd23
dkidd23

This control cannot be made free floating. If you look on the properties for the Calendar and find the property "Placement", it is set at '2'. Changing that to '3' should make it free floating but it doesn't, probably because the control can only be frozen in one place. The only way to make it free floating would be to add a User Form (Custom Dialog Box), insert the calendar into the form then allow the form to be free floating. To do this, do the following: These instructions are for Excel 2003, I don't currently have 2007 installed at home. In a new workbook: Click on Tools - Macro - Visual Basic Editor In VBE Right click on ThisWorkbook in the Project box Select Insert - Then click User Form This will open up UserForm1 and the Controls Toolbox. Right Click on the Toolbox Click on Additonal Controls Scroll Down until you find Calendar Control 11.0 Check the box then click ok which will add the calendar control into the Toolbox. Now click on UserForm1 In the properties Box, change the Caption from UserForm1 to read Calendar In the Control Toolbox, click the Calendar control then click inside UserForm1 Arrange the calendar and box to the size you want. Now double click the Calendar Control and type in Private Sub Calendar1_Click() ActiveCell = Calendar1.Value UserForm1.Hide End Sub Go back to your workbook Click Tools - Macro In the Macro Name box type - ShowCalendar Then Click the Create Button which will open up a new code window. Between Sub ShowCalendar() and End Sub put the following: Sub ShowCalendar() UserForm1.Show End Sub Close Visual Basic Editor and return to your workbook. Click in the cell you want a date. Then Click on Tools - Macro and select ShowCalendar Double Click it or click Run. Select your date. Calendar goes away.

cphillips
cphillips

You can click and drag the calendar while you are in design mode. After you close design mode the calendar is anchored. Hope someone posts a solution.

pgurney
pgurney

When I took out the description of the action and just used the code for the action, it works now.

cphillips
cphillips

I agree, I tried selecting the move with cell & don't move with cell options under the properties tab under format control but that didn't resolve the issue.

sandra.scrivens
sandra.scrivens

If you want to just enter today's date use the shorcut key CTRL+; to get the time use CTRL+: (remember to hold the SHIFT key too!)

bobjorg
bobjorg

That will update to the current date and time whenever the sheet is opened. To keep the original date and time add the following code after the ActiveCell.FormulaR1C1=Now() ActiveCell.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues Application.CutCopyMode = False That will chasnge the formula to the inserted value and keep it from updating whenever you reopen the worksheet. The last line removes the marching ants from around the cell. If you only want the date, replace the Now() with Today().

bruce
bruce

Is there a way to hide the calendar until you need it?

jruby
jruby

If not, go to Excel Options, enable it under the "Popular" menu. Under the developer tab you can get to the calendar control via "Insert" and "More controls". Hope that helps. Jim

cphillips
cphillips

Thanks. I was able to follow you instructions in 2007 to create this macro. I also named a hot key to minimize the key strokes requried to run the macro. Works Great!

mcbinder
mcbinder

You can put the cal at the top and split below it. Then scroll the lower part. mcb

21WordWizard
21WordWizard

What I found helpful was to 'freeze panes' after tucking the calendar in the upper right hand corner of my excel (or you can do left if you are still adding columns). That way it's always in the same place as you navigate through your spreadsheet.

elongp
elongp

change the first character on the description line to a straight quote to comment out the line, and it works

jhammons79
jhammons79

I got the a syntax error when I copied the above code. How is this fixed

dkidd23
dkidd23

One way to do it so you have a smaller footprint is that after you create the calendar, then on the Developers Tool Bar click the Command Button and draw it in. Right click on it and in properties, Change the Caption to read, Show Calendar. In the properties on the calendar change visible from True to False. Now double click the Show Calendar button and enter the following: If Calendar1.Visible = True Then Calendar1.Visible = False CommandButton1.Caption = "Show Calendar" Else Calendar1.Visible = True CommandButton1.Caption = "Hide Calendar" End If ------------------------ Make sure you put the button in a location that it will not be hidden by the calendar. When you need it click the button and when your done, click it again.

jhammons79
jhammons79

Private Sub Calendar1_Click() 'Enter clicked date into selected cell.' ActiveCell.Value = Calendar1.Value End Sub the ' was missing at the end of the 2nd line

JFriesenhahn
JFriesenhahn

Amesko, If you are still looking for a way to check for any date format with an empty cell, the below code I threw together does the trick. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim EraseMe As Boolean EraseMe = False If IsEmpty(Target) Then Target.Value = 1 EraseMe = True End If With Calender1 If IsDate(Target.Value) Then If EraseMe Then Target.ClearContents .Left = ActiveCell.Left + ActiveCell.Width + 5 .Top = ActiveCell.Top - 24 .Visible = True Else: .Visible = False End If End With End Sub

amesko
amesko

I like your neat tweak, Mark; thanks. I also found a way to make the calendar visible when selecting blank cells, which are formatted as dates, using the following IF clause: Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Calendar1 If IsDate(ActiveCell.Value) Or ActiveCell.NumberFormat = "m/d/yyyy" Then .Left = ActiveCell.Left + ActiveCell.Width + 5 .Top = ActiveCell.Top - 24 .Visible = True Else .Visible = False End If End With End Sub One drawback is the NumberFormat property can be only one particular date format, such as "m/d/yyyy." I'd love to make the calendar visible when the active cell has ANY "Date" format, regardless of the specific format code; let me know if you discover anything like this. Thanks again for the tip!

mark
mark

Protect cells you don't want accessed, then protect the sheet. This'll have the claendar popping up in cells that you want it to.

mark
mark

I moved the spacing a little to move the calendar over tot he right a bit, and to lift the calendar so that the header info is aligned above the cell in question. The idea is to make it easier to format the cell using the Fill Handle, and to have less distance to move down when selecting a date. Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Calendar1 If IsDate(ActiveCell.Value) Then .Left = ActiveCell.Left + ActiveCell.Width + 5 .Top = ActiveCell.Top - 24 .Visible = True Else .Visible = False End If End With End Sub

mark
mark

Really nice!

ssharkins
ssharkins

I just posted this code yesterday!

cphillips
cphillips

Thank you for posting this solution. It works very well. I combined using the Hide/Show button with freezing several rows at the top of my worksheet to keep the calendar visible when needed and still allow me to enter data further down the worksheet.

bruce
bruce

Thank you. I'll try this as well.

bruce
bruce

Thanks. I'll try this.

andrewmrichards
andrewmrichards

One other option for showing and hiding the calendar is to write some code in the SelectionChange event, as follows: --- With Calendar1 If IsDate(ActiveCell.Value) then .Left=ActiveCell.Left + ActiveCell.Width .Top=ActiveCell.Top .Visible=True Else .Visible=False End If End With ---- This way, the calendar will only appear in Date cells, and will always appear in the right place. Andrew www.theitservice.co.uk

Editor's Picks