Discussion on:

Cut down on errors by automating an Excel calendar control

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.

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.

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

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

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

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.

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

Thank you. I'll try this as well.

I just posted this code yesterday!

Really nice!

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

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!

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

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.

Thanks. I'll try this.

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

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().

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.

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?

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

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

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

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

Can't click and drag around the spreadsheet?

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.

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.

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

mcb

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.

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!

Can this be done in Excel 2003? thx

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.

Is this available in Excel 2010?

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.