Pop-Up Calendar


Otto Moehrbach

Excel XP & Win XP
I know there is a calendar capability within Excel but I have never used it.
What I am asking for is a place/site to go to to get the information I need.
What I want to do is this:
The user clicks on a cell, firing a Worksheet_SelectionChange macro.
This will cause a calendar to pop up.
The user clicks on a date and that date is entered into that cell and the
calendar disappears.
Any help would be appreciated. Thanks for your time. Otto


following will produce popup calendar in all rows col A - change as required.
Paste in code page of required worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
With Calendar1
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
.Left = Target.Left + Target.Width - Calendar1.Width
.Top = Target.Top + Target.Height
.Visible = True
' select Today's date in the Calendar
.Value = Date
ElseIf .Visible Then .Visible = False
End If
End With
End Sub

Private Sub Calendar1_Click()
With ActiveCell
.Value = CDbl(Calendar1.Value)
.NumberFormat = "mm/dd/yyyy"
End With
If Calendar1.Visible Then Calendar1.Visible = False
End Sub


1. open the VB editor in Excel and do Insert, Userform. you get a
blank form called Userform1 and the control toolbox. right click the
control toolbox and select Additional Controls. Scroll down the list
and if you see Calendar Control then select it. Drag the calendar icon
from the toolbox to the userform and resize as required. If you don't
see the Calendar control in the list then it has not been installed.
Talk to your techy people.
2. Also from the toolbox drag a couple of command buttons onto your
userform. Call them OK and Cancel, or whatever you think appropriate.
Double click the OK button and you will get this:

Private Sub CommandButton1_Click()

End Sub

Edit it to give you this

Private Sub CommandButton1_Click()
Selection.Value = Userform1.Calendar1.Value
Unload Me
End Sub

Double click the Cancel button to create this sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

3. For the Worksheet_SelectionChange macro do

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'some stuff restricting Target rnage
End Sub

That should get you started.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question