Create a calendar object in Excel Cell

[Kzeto]

Hi,
I was wondering did anyone try to insert an calendar objcect into an Excel
Cell before.

thank you.

 

[Ron de Bruin]

Do you mean something like this Kzeto

Do Insert-Object from the menubar and place a calendar control on your
sheet.
It is possible you don't see it in the list, it is installed with Access.
So if you don't have that you possible don't have the control


Here a example how to use it

Place this in a Sheetmodule
If you select a cell in Column A the calendar will popup and when
you DblClick on the calendar the date will be placed in the activecell

Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
Else: Calendar1.Visible = False
End If
End Sub


Example for one cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
Else: Calendar1.Visible = False
End If
End Sub

 

[Les]

I have, and following works for all Workbooks OK for me. I am
using MS Office 2000

Create a form and place a Calendar Control 9.0 which you can find
from the Tools/Additional Controls/Calendar Control 9.0 menu.

Then in the Calendar Code I have
-----------------------------------------
Private Sub Calendar1_Click() ' what happens when user clicks on
control
With ActiveCell
.NumberFormat = "dd-mmm-yyyy" ' format to my preference
.Value = Format(Calendar1.Value, "dd-mmm-yyyy") ' insert
clicked date
End With
Unload Me ' closes and unloads Calendar
End Sub
-----------------------------------------
Private Sub UserForm_Initialize() ' what happens when user opens
form containing Calendar
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value) ' uses cell
value if date
Else
Calendar1.Value = Date ' uses today's date as default
End If
End Sub

-----------------------------------------
and in a general module I have
-----------------------------------------
Private Sub OpenCalander() ' user clicks button to open Calendar
frmCalendar.Show ' shows Calendar
End Sub
-----------------------------------------

I have all this in Personal.xls and a menu button to run the
OpenCalendar sub which works on all opened workbooks.

regards,