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?

[Nimrod]

TJ:
I think I've been able to create something that will meet your needs nicely... however it will require about 5 lines of VBA code ... is that OK ? If so continue to read.

The Resulting Method
( This is the result if the VBA code and form is produced )
When you want a date in a particular cell
1- Click once on the cell where you would like date
2- Press Ctrl+d ( a calender will appear)
3- Double click on date in Calender
4- Date is entered in selected cell
5- Calender disappears

HOW TO MAKE IT HAPPEN

First: Make a VBA form
1- Open VBA editor
2- Goto VBA toolBar
3- Select "Insert" from VBA toolbar
4- Select "UserForm" from drop down menu
(you have created a userform called "userform1"

Second: Put calander on userform
1-In VBA window select the "toolbox"
2- Right click on "toolbox"
3- Select "additional controls" item from drop down menu
4- check/select "calender control" from the window that will appear
5- a calender object will appear on the "toolbox"
6- Drag and size the calender object from the "toolbox" to "userform1"

Third : Write VBA code for calender
1- Once calender is on userform1 then "double click" the calender...the VBA code window for the calender will open
2- Insert the following code

Private Sub Calendar1_DblClick()

ActiveCell.Value = Calendar1.Value
UserForm1.Hide
Unload UserForm1
End Sub

Fourth Write code to make calender appear
-1 In VBA window select "insert" from VBA toolbar
-2 From drop down menu select "Module"
-3 In the new VBA "Module" window that will appear paste the following code

Public Sub GetDate()
Load UserForm1
UserForm1.Show
End Sub

Fifth : Assign Shortcut key to Macro
-1 Close VBA window with the X in upper right corner
-2 You should now be looking at excel worksheet
-3 On excel toolbar select "Tools"
-4 From drop down menu select "Macro"
-5 From next drop down select "Macros..."
-6 From the list of macros that appear select the name "GetDate"
-7 Select the "command button " in the same window labeled "Options"
-8 Assign the shortcut key for Macro to be "Ctrl+d"
-Press OK and exit window

IT IS NOW READY FOR USE

_________________
NOTE: (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)
Adieu,Nimrod
[ This Message was edited by: Nimrod on 2002-08-02 21:44 ]

[Brian from Maui]

Nimrod,

It's past midnight here, and I'm surfing this board. Came across your contribution and tried it...awesome...thanks

Brian

[TJ]

Nimrod,

I'll try it & let you know. Thanks Much!!

[TJ]

Nimrod,

Just tried it and it works great. Many thanks for your help, you've saved me invaluable time.

TJ

[Yogi Anand]

Hi Nimrod:

Thanks for your contribution ... works Great!

Regards!

Yogi

[AC]

Nimrod, I finally got the calendar control to load on my computer, when the form comes up should the calendar be on today’s date? Mine is not. Thanks

[nanefy]

Hi,

I have used the calander function below and it works perfectly. The only problem is that it doesnt, by default, bring up the current month. I implemented this in September and now in October it doesnt bring October up as the default date. Can anyone tell me how to do this?

Cheers!

[UHsoccer]

Nimrod - The instructions were very well organized and it worked on the first try

Is there a way to double click on the cell and have the calendar form pop-up?

[UHsoccer]

I am trying the following

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim Cell1 As String, Cell2 As String
    Cell1 = "$F$20"  
    Cell2 = "$F$25"  
    If Target.Address = Cell1 Or Target.Address = Cell2 Then
        ActiveCell.Value = Calendar1.Value
        UserForm2.Hide
        Unload UserForm2
    End If
    
End Sub

It debugs to Calendar1 "Variable not defined".

How does one define that?