Wayne has attended:
Excel Advanced course
Cell calendar excel 2007
How can I place a drop down calendar in a cell?
RE: Cell calendar excel 2007
Hello Wayne,
Hope you enjoyed your Microsoft Excel Advanced course with Best STL.
Thank you for your question regarding how to place a drop-down calendar into a cell. Actually there is no way to do this simply without going to VBA programming. You can however, fool Excel into thinking that a cell has a drop-down calendar by following these steps:
1) Add the Developer ribbon (Office button > Excel Options > Popular section)
2) Under the Controls group of the Developer ribbon click the Insert button
3) Click the 'More Controls' button at the bottom right side
4) Locate and click the Microsoft Date & Time Picker Control
5) Click and drag a small rectangle onto the spreadsheet (about the size of a cell)
6) In the Controls group the Design Mode button should already be highlighted... now click the Properties button
7) We are interested in the Linked Cell option... type the cell reference which should change whenever a calendar date is selected
8) You will get a message about the checkbox shouldn't be false etc.... ignore this and close this message box
9) Deselect Design Mode and where you dragged the calendar control there will be a drop-down which when clicked will give you a calendar. Clicking a date will now populate the linked cell with the chosen date
10) If you wish, you can click Design Mode again and then drag the Calendar control over the linked cell... then deselect design Mode
11) Once you save and then close the file and then re-open it, you will notice that the controls seem to have changed shape! All you need do is to minimise Excel then maximise... this usually fixes the shape back to what it was originally.
I have attached a sample file containing 2 cells which are controlled by the Microsoft Date & Time Picker control...
...try this out and see whether this is what you are looking for.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Attached files...
Calendar Drop Down.xlsxRE: Cell calendar excel 2007
Hi Rodney
Right, I have a confusing statement coming from Excel
It says I "cannot insert object"
I have tried this on another machine here and it works fine
Therefore it must be a security setting, I tried this same link from the net before and had the same issues
Coudl you advise on what security I need to disable, I have tried a few things including the settings in the Macro set-up
look forward to hearing from you soon
Best regards
Wayne
RE: Cell calendar excel 2007
Hello Wayne,
I'm afraid some computers are set so that they don't allow issues which may pose a security risk. So don't try to open my file and create the drop downs yourself with the instructions I have given you. If you cannot find the Microsoft Date & Time Picker control then it has been disabled on your computer. You will then have to speak to your IT department about this.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
RE: Cell calendar excel 2007
Hello Rodney
Thanks for the quiock response. The Microsoft Data and time picker can be accessed on my excel so it is possible
Its just when I try to create the window "around the size of a cell" the statement "cannot insert object" is shown
It works on another laptop here but I cannot get it to work on mine, therefore I think its a settings issue rather than a fundamental problem
I will ask my IT department for some assistance ,however i do think its a settigns issue
Many thanks
Best regards
Wayne
RE: Cell calendar excel 2007
Hello Wayne,
You are probably correct about the issue having to do with settings. Check the ActiveX settings in Excel Options > Trust Center > Trust Center Settings > ActiveX Settings. I use the third setting from the top.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
