Calendar

[wzel]

Is it possible to format a cell so that when it is clicked on, a drop down calendar displays and you can choose the date from the calendar to enter into the cell? If so, could someone describe the procedure. Thank You.

[Ricky Morris]

Try:

Choose Data > Validation form the menu. Choose List from the Allow box. In the source box highlight a range on the spreadsheet which contains the data you would like to appear in your list.

[dwhj]

Alternatively, you could use Excel's calendar control.

Go to the VBE (Alt+F11), click on Tools>Additional Controls and look for Calendar Control 9.0.
Put this control on a UserForm and also put two spinner buttons and a command button.

Put the following in the UserForm code module :-

Private Sub CommandButton1_Click()
ActiveSheet.Range("A1") = Calendar1.Value
Me.Hide
End Sub

Private Sub SpinButton1_Change()
If Calendar1.Month = 12 And SpinButton1.Value > 0 Then
Calendar1.Year = Calendar1.Year + 1
Calendar1.Month = 1
ElseIf Calendar1.Month = 1 And SpinButton1.Value < 0 Then
Calendar1.Year = Calendar1.Year - 1
Calendar1.Month = 12
Else
Calendar1.Month = Calendar1.Month + SpinButton1.Value
End If
SpinButton1.Value = 0
End Sub

Private Sub SpinButton2_Change()
Calendar1.Year = Calendar1.Year + SpinButton2.Value
SpinButton2.Value = 0
End Sub


Put the following in the Worksheet's code module :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then UserForm1.Show
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
UserForm1.Show
Target.Offset(1, 0).Select
End Sub


Now whenever cell A1 is selected or double-clicked, the calendar will appear so that a date can be selected and then entered in A1 by clicking the command button.

[Fonged]

Thanks for the post dwhj, I am trying to do it as well.

Could you make it a bit more dynamic - I need the calendar to come up and enter the date when any cell of a specific column is double clicked.

Thanks for the help.

[dwhj]

On 2002-05-06 17:18, Fonged wrote:
Thanks for the post dwhj, I am trying to do it as well.

Could you make it a bit more dynamic - I need the calendar to come up and enter the date when any cell of a specific column is double clicked.

Thanks for the help.

Actually there was an error in the original code.
The BeforeDoubleClick procedure should have been :-

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$1" Then
UserForm1.Show
Application.EnableEvents = False
Target.Offset(1, 0).Select
Application.EnableEvents = True
End If
End Sub


If you want the calendar to appear, for example, when any cell in Column 2 (i.e. column B) is double-clicked :-

Private Sub CommandButton1_Click()
ActiveCell = Calendar1.Value
Me.Hide
End Sub

Private Sub SpinButton1_Change()
'MsgBox SpinButton1.Value
If Calendar1.Month = 12 And SpinButton1.Value > 0 Then
Calendar1.Year = Calendar1.Year + 1
Calendar1.Month = 1
ElseIf Calendar1.Month = 1 And SpinButton1.Value < 0 Then
Calendar1.Year = Calendar1.Year - 1
Calendar1.Month = 12
Else
Calendar1.Month = Calendar1.Month + SpinButton1.Value
End If
SpinButton1.Value = 0
End Sub

Private Sub SpinButton2_Change()
Calendar1.Year = Calendar1.Year + SpinButton2.Value
SpinButton2.Value = 0
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 2 Then
UserForm1.Show
Target.Offset(1, 0).Select
End If
End Sub


In the BeforeDoubleClick procedure, just change the column number to fit your needs.



[ This Message was edited by: dwhj on 2002-05-06 18:36 ]

[Eddie G.]

dwhj: This is cool, except when you click the spinner button to go in reverse, nothing happens, the forward button to increase the dates works fine.

[dwhj]

On 2002-05-06 19:12, Eddie G. wrote:
dwhj: This is cool, except when you click the spinner button to go in reverse, nothing happens, the forward button to increase the dates works fine.

In the VBE go to the spin button properties window, and change the "Min" property from 0 to -1

[smart]

the Additional Controls it's unenable

how i can let Enable
really nice Topic

[ This Message was edited by: smart on 2002-05-07 05:51 ]

[Rocky E]

Hi Smart,
Did you Insert->User Form? It doesn't appear enabled until I do.


_________________
Hope this helps!

Rocky

"Be not the first by whom the New are try'd,
Nor yet the last to lay the Old aside."
Alexander Pope (1688-1744).

[ This Message was edited by: Rocky E on 2002-05-07 06:17 ]

[dwhj]

On 2002-05-07 05:49, smart wrote:
the Additional Controls it's unenable

how i can let Enable
really nice Topic

[ This Message was edited by: smart on 2002-05-07 05:51 ]

I'll assume you're using Excel 2000.

In the VBE, go to Tools>References and look in the drop down list for Microsoft Calendar Control 9.0
If it's there, select it.

If it's not there, create a reference by browsing to MSCAL.OCX
Then go to Tools>Additional Controls and activate Calendar Control 9.0


Note : If you are using Excel 97, you need to activate Calendar Control 8.0