calendar

[dyowell]

I have a worksheet that has a date column. I would like
to be able to click in the column and bring up a calendar
view to choose the date. I have no idea how to do this,
but thought that maybe someone could help lead me in the
right direction. Thanks in advance for the help.

 

[Ron de Bruin]

Hi dyowell

Maybe this will help
http://www.rondebruin.nl/calendar.htm

 

[Norman Jones]

Hi Dyowell,

Try:


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

 

[Nigel]

On sheet1 insert a calendar control - from the menu choose Insert->Object
and from the list 'Calendar Control 8.0' the actual version number may vary.
In design mode you can format this and set defaults etc., using the
properties, Right Click, choose Calendar Object->Properties.

Behind sheet1 place the following code

Private Sub Calendar1_Click()
ActiveCell.Value = Calendar1.Value
Calendar1.Visible = False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Calendar1.Visible = True
End If
End Sub

The above code will hide the control until a cell in column 1 ("A") is
clicked, the calendar will becoem visible allow you to change year and
month, when you click on the actual day the value will be placed in the cell
in colum 1 you clicked.

Hope this provides a template.

Cheers
Nigel

 

[dyowell]

-----Original Message-----
On sheet1 insert a calendar control - from the menu choose Insert->Object
and from the list 'Calendar Control 8.0' the actual version number may vary.
In design mode you can format this and set defaults etc., using the
properties, Right Click, choose Calendar Object-
Properties.

Behind sheet1 place the following code

Private Sub Calendar1_Click()
ActiveCell.Value = Calendar1.Value
Calendar1.Visible = False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Calendar1.Visible = True
End If
End Sub

The above code will hide the control until a cell in column 1 ("A") is
clicked, the calendar will becoem visible allow you to change year and
month, when you click on the actual day the value will be placed in the cell
in colum 1 you clicked.

Hope this provides a template.

Cheers
Nigel





.
Thanks for the help. Nigel, I am able to get the calendar

on my worksheet. However, the calendar seems to be
frozen. When you say to go to design mode, I'm not sure
what you are talking about. Could you please let me know
how to get to design mode? Thanks.

 

[Nigel]

Goto menu View->Toolbars and activate the 'Control Toolbox' - the icon (by
default) on the left is labelled design mode, click this and the controls on
the worksheet can be edited and moved around. (unlocked in your terms).

Cheers
Nigel

 

[Guest]

Hi Ron,
Thanx for the great routine for calendar popup in an excell cell.
I have three issues:
a) The routine doesnt work with merged cell, i.e. it doesnt show the
calendar when needed in a merged cell nor does it hide the calendar when the
cursor moves to a merged cell
b) How do I show calendar popup at different places in the worksheet?
c) How do I distribute calendar.ocx with my template?

Please help.

- Raju Tolani

 

[Ron de Bruin]

Hi Raju

A)
Never use Merge cells, always trouble(for example if you want to filter)

B)
You can use this
If Not Application.Intersect(Range("A1:A20,C1:C20"), Target) Is Nothing Then

C)
This is not allowed as far as I know