Drop-Down Box with a Calendar

[TJ]

Is there a way to set up a designated cell in a spreadsheet with a drop-down box that will have a calendar, so that when you click on a date in the calendar it puts that date in the cell?

 

[imranhusain]

Thats Great !

The way you explaned is terrefic.

Keep it up

Thanks

 

[babycody]

I was looking for something similar and found this tutorial. Uses current date, and the calendar pops up when the cell is selected. Hope this helps someone. http://www.rondebruin.nl/calendar.htm

 

[noppojp]

i love this little calendar. So do many other people i work with.

i'd like to 2nd this question.

** Is there any way to make it reflect today's date?? *** ray:

Would love some help on this. A simple point in the right direction would do.

Regards from Tokyo

 

[thomachr]

i love this little calendar. So do many other people i work with.

i'd like to 2nd this question.

** Is there any way to make it reflect today's date?? *** ray:

Would love some help on this. A simple point in the right direction would do.

Regards from Tokyo

I have been looking at a number of calendar control threads and have come up with the following code which is MOSTLY doing exactly what I want:

Private Sub Calendar1_DblClick()
    ActiveCell.NumberFormat = "mm/dd/yyyy"
    ActiveCell = Calendar1
    Calendar1.Visible = False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("C3:C5"), Target) Is Nothing Then
            Calendar1.Left = Target.Left + (Target.Width * 1.1)
            Calendar1.Top = Target.Top
        Calendar1.Visible = True
        Calendar1.Value = Date ' set calendar to today's date
    ElseIf Calendar1.Visible Then Calendar1.Visible = False
    End If
End Sub

Hope it helps!

the only think I can't get to work is I only want the calendar to appear in cells C3 and C5, but not C4. Any idea how to edit the range appropriately?

 

[noppojp]

Hmmm. i don't know how to skip C4 in the above code.
i used the code you provided and i get an error:

Runtime Error'424': object required

----i have the following in my sheet. Maybe there's a conflict with the first one??? ----

Any ideas to get it to work?


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If IsDate(Target) Or Target.Value = "mm/dd/yy" Then GetDate
End Sub

Private Sub Calendar1_DblClick()
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell = Calendar1
Calendar1.Visible = False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("C3:C5"), Target) Is Nothing Then
Calendar1.Left = Target.Left + (Target.Width * 1.1)
Calendar1.Top = Target.Top
Calendar1.Visible = True
Calendar1.Value = Date ' set calendar to today's date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub

 

[thomachr]

Hmmm. i don't know how to skip C4 in the above code.
i used the code you provided and i get an error:

Runtime Error'424': object required

----i have the following in my sheet. Maybe there's a conflict with the first one??? ----

Any ideas to get it to work?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If IsDate(Target) Or Target.Value = "mm/dd/yy" Then GetDate
End Sub

Private Sub Calendar1_DblClick()
    ActiveCell.NumberFormat = "mm/dd/yy"
    ActiveCell = Calendar1
    Calendar1.Visible = False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("C3:C5"), Target) Is Nothing Then
            Calendar1.Left = Target.Left + (Target.Width * 1.1)
            Calendar1.Top = Target.Top
        Calendar1.Visible = True
        Calendar1.Value = Date ' set calendar to today's date
    ElseIf Calendar1.Visible Then Calendar1.Visible = False
    End If
End Sub

I'm not really sure what might be causing your error. To get it to work, I had to place the code on the sheet object as opposed to a module. Also you need to add the calendar object to the sheet you want to use the calendar on: Insert/Object/Calendar Control 11.0

Sorry I can't help any more than that.[/code]

 

[VISH9821]

how can i apply this to more than one column!!!!!!!!!!!!