put a calendar on an excel sheet

[M K W]

Hello,
I want a cell to be a calendar so that when I click on it or its small
arrow, a calendar appears and select the date I want. Is it an activex? if
yes, can anyone provide me with a good one?
thanx alot

 

[Ron de Bruin]

You need VBA to do this

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 to use

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 Not Application.Intersect(Range("a1"), Target) Is Nothing 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

 

[M K W]

Thank you very much Ron de Bruin, I really appreciate that.

 

[Orlando Magalhães Filho]

Hi,

Look for "Excel popup Calendar" at this site:
http://cpap.com.br/orlando

To calendar popup when a certain cell and certain sheet is
selected try this:
- Open your workbook
- On worksheet tab, at bottom Excel window, right click the sheet of your
target range;
- At popup menu click View code;
- Insert the code below that popup calculator when A1 cell is selected and
ExcelCalendar.xla is load;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
Application.Run "ExcelCalendar.xla!Calendar"
End Sub

HTH