Calendar

wzel

New Member
Joined
May 5, 2002
Messages
9
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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top