Adding Calendar to Worksheet

[tracktor]

I want to add a calender to my worksheet that when a user selects a specific
cell the calender will come up, the user can select a date, and it will fill
the cell with that date, then when the user goes to the next cell the
calender dissapears, but the date stays in the specific cell.

 

[Rick Rothstein \(MVP - VB\)]

If you do not already have the Visual Basic toolbar displayed, click
View/Toolbars from Excel's menu bar and select it from the list of Toolbars
available. Next, click its Control Toolbox icon (looks like a wrench and
hammer crossing each other). Next, click the More Controls icon on the
Control Toolbox (also looks like a wrench and hammer crossing each other)
and select Microsoft Month View Control from the list, then click on the
worksheet to place it. Move it near the cell you want it to become visible
for when selected. Right click the MonthView Control and select Properties
from the popup list that appears. Set the Visible property to False. Also,
you can control the size of the MonthView Control by changing its Font Size
(click Font, then the ellipsis button). You can now dismiss the Properties
window and double click the MonthView Control to get into the VBA editor.
Copy/Paste this code in the code window that appeared...

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
Range("H5").Value = MonthView1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MonthView1 .Visible = Target.Address = "$H$5"
End Sub

Note: Change the "H5" and "$H$5" example values to the cell address you want
the functionality for (the addresses must be the same and the one in the
SelectionChange event *must* be an absolute address. Go back to the
worksheet and turn off Design Mode and dismiss the Control Toolbox (click
their icons on the Visual Basic toolbar). That's it... it should work
automatically from here on out.

Rick

 

[tracktor]

This did not work. I get a compile error. Invalid or unqualified reference.
Here is the code that I put in the VB Editor:

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
Range("H2").Value = MonthView1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MonthView1 .Visible = Target.Address = "$H$2"
End Sub

This line is highlited yellow:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

[Gord Dibben]

See how Ron de Bruin does it.

http://www.rondebruin.nl/calendar.htm


Gord Dibben MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

The control works on my system... perhaps the MonthView control came with my
version of compiled VB and is not usable unless you have a "legitimate" copy
of compiled VB installed on your system (although I did not think that was
the case). There is another calendar control available from the same More
Tools icon in the Control Toolbox... it is called, appropriately enough,
Calendar Control X.0 where on my system the X is 12... I'm assuming the
version number changes with the version of Excel installed. You can follow
all of the same instructions as I gave you earlier (except for the Font
object sizing one... this control has separate Font objects for its Title
and Day text, so you will need to change both of those in order to make the
calendar look good at different sizes). As for code, it is basically the
same...

Private Sub Calendar1_Click()
Range("H5").Value = Calendar1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calendar1.Visible = Target.Address = "$H$5"
End Sub

Give that a try and see if it works for you.

Rick

 

[Rick Rothstein \(MVP - VB\)]

... (except for the Font object sizing one... this control has separate

Font objects for its Title and Day text...

Just noticed... it also has a font object for the Grid text also.

Rick

 

[tracktor]

As far as I know my copy of Microsoft Office is legitamate, I bought it form
the University’s bookstore, and it is Microsoft Professional Edition 2003,
and it says “Academic Price, Not for use in a commercial environment.†As far
as I know it is a full blown version that was sold at a reduced price for
students.

I put the Calendar Control 11..0 and it works fine on an empty worksheet.
But when I put it on the worksheet with the other code that you gave me for
“emptying the contents of a cell based on the value of another cell†post. I
get a message that says “ Compile error: Ambiguous name detected:
Worksheet_SelectionChange

This is exactly what is pasted in the View Code window before I pasted the
code for the calendar:

'********************** START OF CODE **********************

Dim PreviousValue As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$233" Then
If Range("H233").Value = "See page 10 Additional Terms" And
Range("H235").Value >= "0" And _
PreviousValue = "Enter Specific COE Date" Then Range("H235").Value = ""
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$H$233" Then PreviousValue = Target.Value
End Sub

'*********************** END OF CODE ***********************

This is what it looks like after I pasted the code for the calendar.

'********************** START OF CODE **********************

Dim PreviousValue As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$233" Then
If Range("H233").Value = "See page 10 Additional Terms" And
Range("H235").Value >= "0" And _
PreviousValue = "Enter Specific COE Date" Then Range("H235").Value = ""
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$H$233" Then PreviousValue = Target.Value
End Sub

'*********************** END OF CODE ***********************
Private Sub Calendar1_Click()
Range("H2").Value = Calendar1.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calendar1.Visible = Target.Address = "$H$2"
End Sub

 

[Gord Dibben]

You cannot have two events of the same type in the same worksheet module.

Looks like you are trying for too many Selection_Changes.

Also looks like too many Worksheet_Changes


Gord Dibben MS Excel MVP

 

[tracktor]

I have more than one place to enter a date, so I need a calendar to show up
in those places when the user selects that cell. I also have one of them that
I want the cell to be emptied based on the value of another cell. I'm sure
this could be done. It's obvious I don't know how to code.

For right now when a user selects cells H2, H59, and H213, I need a calendar
to pop up so they can select a date, and as soon as they do I need the
calendar to dissapear. All three of the dates are different. The date in cell
H213 needs to be emptied based on the value of another cell.

 

[Ron de Bruin]

See
http://www.rondebruin.nl/calendar.htm

Note: You can use this if your range is not one area
If Not Application.Intersect(Range("A1:A20,C1,E1"), Target) Is Nothing Then

You can add a if statement between this if do do something different for a specific cell