Drop-Down Box with a Calendar

TJ

New Member
Joined
Aug 1, 2002
Messages
3
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
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
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

_________________
<font size=-1>NOTE:</font.<font size=-1> (Testing performed on Win2K utilizing Office 2000. Solutions may need tweaking for other versions.)</font><font size=+1><font color="blue">Adieu,<font color="red">N<font color="blue">imrod</font
This message was edited by Nimrod on 2002-08-02 21:44
 
Upvote 0
Nimrod,

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

Brian
 
Upvote 0
Nimrod,

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

TJ
 
Upvote 0
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
 
Upvote 0
Calander Problems

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!
 
Upvote 0
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?
 
Upvote 0
I am trying the following

Code:
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?
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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