Calendar in two columns or specified range

[William J. Szabo]

I have used the following which works fine except that I would like to
have the calendar available for two specific columns or a specified
range. What is the syntax to replace the "If Target.Column = 1" to
accomplish this.

 

[Ron de Bruin]

Hi William

Try this for
Range("A1:A20,C1:C20")

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


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

I also add some new stuff on the page this year
http://www.rondebruin.nl/calendar.htm

 

[William J. Szabo]

Thanks Ron, that did the trick. Now I am trying to get the "Calendar"
to co-exist with the "Date Time Picker" control and am having problems
with them both being functional on the same sheet. I can get them to
work on separate sheets but then I tried to cut and paste the code
from the functional sheets into one sheet and that doesn't work (code
below). In essence what I am trying to accomplish is to be able to
select a date from the calendar in column "A" and the start time and
end time in column "E" and "F" respectively. Thanks again for your
help.

Bill

Private Sub DTPicker1_DblClick()
ActiveCell.NumberFormat = "h:mm AM/PM"
ActiveCell = DTPicker1
Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "m/d/yyyy"
ActiveCell = Calendar1
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("e1:f20"), Target) Is Nothing
Then
DTPicker1.Left = Target.Left + Target.Width -
DTPicker1.Width
DTPicker1.Top = Target.Top + Target.Height
DTPicker1.Visible = True
Else: DTPicker1.Visible = False
End If
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

 

[Ron de Bruin]

Hi William

Why you need both?
I will test it tomorrow for you

 

[William J. Szabo]

Ron,

I need for the Calendar control to format the cell entry:
ActiveCell.NumberFormat = "m/d/yyyy"

and the Date Time Picker control to format the cell entry:
ActiveCell.NumberFormat = "h:mm AM/PM"

Also I have set the properties of the individual controls so that they
perform differently. If there is a way to do this with one control
I'll certainly try it.

Thanks again for your help on this.
William Szabo

 

[Ron de Bruin]

Hi William

I have no system with the Date Time Picker control on this moment to test it for you
Maybe there is anybody else that can try it for you

Sorry

 

[William J. Szabo]

Ron,

Thanks for the help, if you want I can email the Excel file that I am
working on but I don't know if that will help or not. Again, your
help to date has been most appreciated.

Thank you,
Bill Szabo
Plan-Tech