Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a calendar object in Excel Cell
Hi,
I was wondering did anyone try to insert an calendar objcect into an Excel Cell before. thank you. -- Khai Mun, Ng ----------------------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a calendar object in Excel Cell
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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kzeto" wrote in message ... Hi, I was wondering did anyone try to insert an calendar objcect into an Excel Cell before. thank you. -- Khai Mun, Ng ----------------------------------------------------------- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a calendar object in Excel Cell
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, -- Les Hay, Livingston. Scotland "Kzeto" wrote in message ... Hi, I was wondering did anyone try to insert an calendar objcect into an Excel Cell before. thank you. -- Khai Mun, Ng ----------------------------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calendar Object in Excel | Excel Discussion (Misc queries) | |||
I want to link a cell in excel to calendar object? | Excel Worksheet Functions | |||
How do I create a Calendar Drop Down menu for a Validation cell | Excel Worksheet Functions | |||
How do I create a workbook that links info in cell to a calendar? | Excel Worksheet Functions | |||
Is there any way to create a drop down calendar into a cell in ex. | Excel Discussion (Misc queries) |