TJ
Welcome to the Board


Joined: 02 Aug 2002
Posts: 3


Status: Offline

   

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
MrExcel MVP


Joined: 30 Apr 2002
Posts: 2511
Location: Vancouver BC , Canada

Status: Offline

   

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

---

code:

---

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

---

Brian from Maui
Board Master



Joined: 17 Feb 2002
Posts: 2380

Flag:

Status: Offline

   

Nimrod,

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

Brian

TJ
Welcome to the Board


Joined: 02 Aug 2002
Posts: 3


Status: Offline

   

Nimrod,

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

TJ
Welcome to the Board


Joined: 02 Aug 2002
Posts: 3


Status: Offline

   

Nimrod,

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

TJ

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 5440
Location: Michigan USA

Status: Offline

   

Hi Nimrod:

Thanks for your contribution ... works Great!

Regards!

Yogi

AC
Board Master


Joined: 22 Mar 2002
Posts: 102


Status: Offline

   

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