ho do i insert calendar into xls worksheet

[Ron de Bruin]

I think I do this

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

 

[Guest]

Hi Ron,

it works excelent. You are the best )

Thanks a lot.

Drahos.

 

[Ron de Bruin]

You are welcome

 

[Guest]

Ron;
I got the same error message that Drahos had.
What do you mean with "Maybe the Calendar you have add to the worksheet is
not named Calendar1". How and where can I change this name?

Thanks in advance.
Maperalia

 

[Ron de Bruin]

hi maperalia

If you go in Edit mode (button on the control toolbox toolbar) and select the calendar you can see the name in the formulabar.
You can change it there also

 

[Guest]

Ron;
I got it. Thanks you very much!!!!!!!
I have found that you have been helping me in a lot of questions I have been
having in this wonderful site.
i really appreciatte you supporting in VBA.

Kind regards.
Maperalia

 

[Guest]

Hi Ron,

Thanks for displaying the information on how to load a calendar.
I have a couple of questions if you can help please?

Is there a way of when you click on the cell the calendar pops up to only
select the date once via the mouse and the pop up calendar disappears rather
than you clicking enter or clicking on other cell?
Also I am having problems changing the date format to UK - I have changed
this in the format cells section, but it reverts back to US version for each
date selected? - Is there any way you can lock the format?

Thanks

 

[Ron de Bruin]

Hi Darren

Which Excel version do you use ?
You have a protected sheet ?

 

[Guest]

Hi Ron,

It's 2003 version.
No, It's not protected - I have created a new sheet...

Thanks for looking into this....

regards

 

[Guest]

Hi Ron,

Ignore my previous reply - I've managed to fix the problem of the date - i
didn't see the fomat within the code..
Thanks anyway

But do you know if there is a way of when you select the cell and a date,
just one click on the date to remove the calender rather than selecting
another cell or pressing enter and is there an icon you can use..? (pretty
much similar to a web based calendar form..?)

Thanks in advance..

 

[Ron de Bruin]

Hi Darren

You can use a macro and assign it to a button if you want
Delete the selection change event and use this in the sheet module

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


Copy this in a normal module

Sub Call_Calendar()
With Sheets("Sheet1")
If Not Application.Intersect(.Range("A1:A10"), ActiveCell) Is Nothing Then
.Calendar1.Left = ActiveCell.Left + ActiveCell.Width - .Calendar1.Width
.Calendar1.Top = ActiveCell.Top + ActiveCell.Height
.Calendar1.Visible = True
' select Today's date in the Calendar
.Calendar1.Value = Date
ElseIf .Calendar1.Visible Then .Calendar1.Visible = False
End If
End With
End Sub

 

[Ron de Bruin]

Hi

When you use the code from my page you only have to add the calendar to the worksheet
with Insert Object

Read the page again
http://www.rondebruin.nl/calendar.htm

 

[Ron de Bruin]

Change the range in the code to the range with your date cells

See also this note on my site

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

 

[Ron de Bruin]

Calendar1 is the name of your calendar

When you are in Edit mode you can see the name in the formulabar on the left
Be sure that it is named Calendar1

 

[Ron de Bruin]

Calendar1 is the name of your calendar

When you are in Edit mode you can see and edit the name in the formulabar on the left
Be sure that it is named Calendar1

 

[Ron de Bruin]

Close Excel
Start Excel again and try the code from my site in a new workbook

 

[Ron de Bruin]

Send me your test workbook private and i look at it

 

[Guest]

Hi Ron;
I sorry to bother you again, but I want to make work the calendar in other
computer and does not work.
I noticed that this computer does not have the "calendar control" under the
menu Insert/Object/Create New/"OBJECT TYPE".
Can you tell me how can I add this calendar control in excel?

Thanks in advance.
Maperalia

 

[Ron de Bruin]

Hi maperalia

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

You find a link there where you can download the control

 

[Guest]

Ron;
Thanks for your quick respond.
I have the program load it in the "P" drive. The calendar is working in all
computers except one. This one does not have the "calendar control" under the
menu Insert/Object/Create New/"OBJECT TYPE". I wonder how can I load it in
this computer to make run the program.

Thanks.
Maperalia