Calender Control VB

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hi guys,

Is it possible to set up a windows calender control
attached to a excel cell ? to enable user select a
particular date ?

Thanks

Michael
 
Try this Michael

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 see it 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
 
Back
Top