(112 votes, average: 5.24 out of 6)
Loading ...So you want to create a drop down calendar in Excel? Apart from sending alerts to Outlook from Excel (working on that article as well) this has been the most requested how-to as of late. For example:
Reader Tim Asks:
How do I insert a calendar as a way for a user to click on the cell have the date box open and choose the date that should be in the cell? Also where do I send the beer?
Reader Wayne Asked:
I am sure that I saw a pull down menu in a worksheet cell. I want to have a cell formated so that I can have a pull down menu of a calendar in which to choose the date.
This seems like it would be so easy yet when you look around on-line the answers are all full of VBA scripts and custom code.
I assume you just want someone using the spreadsheet to be able to select a cell, have a calendar drop down and then populate the field with whatever date the person chooses. Yes it can be done. Yes it is fairly involved. And yes, I have a workaround.
Would you like the easiest, cheesiest, sleaziest way to do this? Well then watch this:
The first thing you need to do is enable the Developer Tab in Excel.
- Select the Pearl or “Office Button” in Excel. The Pearl is the ball in the top left corner that gives you the option to Print, Save, etc.
- With the Pearl menu open select Excel Options from the very bottom on the right.
- Another menu will open – Select Popular
- Check the box for “Show Developer Tab in the Ribbon“
Now you have the developer tab with all the controls for forms, xml, code, etc.
Go to the developer tab and select Insert, followed by the little Pink Floyd-esque More Controls icon.
Scroll through the list of controls and look what’s available. There are all sorts of funky add-ins you can use in your spreadsheets! We are looking for the Microsoft Date and Time Picker Control
Highlight it and select OK.
Your mouse will turn in to a cross hair – click and hold, draw a rectangle, release. You now have a date box!
You will also see code in the top akin to “=EMBED(“MSComCtl2.DTPicker.2″,”")”
You will also notice that you are in Design Mode because that icon is highlighted. This allows you to play with the settings for the date box you just created.
Deselect Design Mode and you have a date control! Select the drop down and a calendar appears! Holy Cow that’s super cool awesome but guess what! It doesn’t do anything!
You can click it and change the date as much as you want but nothing else is changing in your spreadsheet!
Here is where the genius of my non-code writing laziness pays off. Ready?
What cell do you want the date to change in? Let’s say C5
- Ok, In B5 type SELECT DATE:
- In D5 enter the formula =C5
- Still got that calendar control visible like in the image above?
- Let’s go back to Design Mode by clicking it. Move the calendar control box up around the C3 area just to move it out of the way.
- Right Click in the calendar control and select Properties.
- A properties menu will open up
- Look for the field Linked Cell and enter C5 (that’s where we want the date change to happen)
- You might get an error message – ignore it!
- Close the Properties menu
- Deselect Design Mode
Here’s where it all comes together. .
- Select the Date Drop down and change the date
- You will see Cell C5 change to that date
- You will see cell D5 (which =C5) change as well. The D5 was just to ensure that C5 was actually showing a date.
- Repeat step 1-3 until you are as giddy as a little girl.
Last Question: How do I use this?
Well you can create as many of these drop downs as you like. You can tell each one what cell to update (Linked Cell) and you can move the Date Control right over the top of the field it is updating! Think about that – Move the date picker right over the top of the cell it is linked to. Only you know the truth! There is no advanced code, you’re just hiding the data in C5 under the Date control that is updating or controlling C5! Then do any date calculations you need to do using C5 as the reference!
I told you it was sleazy, cheesy and easy!
I’ll make it even easier – here’s the spreadsheet I used to write this tutorial and take screen shots
Excel Drop Down Date Example (12k)
And Tim, You any any other reader can always send the beer to me by clicking beer mug at the top of the page.
December 4th, 2008 at 12:05 pm
Well, that's a really neat trick. Do you have any advice on printing? Such as making the drop down date thing invisible while printing. I'd like the date on a printed form, but it shows both the linked cell and the drop down box. I can make the regular cell invisible, but not the drop down box and the drop down box has that thing on the corner.
Thanks.
December 4th, 2008 at 12:14 pm
Sorry, I should have looked before posting. In design mode right click and choose "Format Control" then "Properties" tab, just deselect the print object box and it won't show up when you print.
December 4th, 2008 at 6:44 pm
Excellent! Thanks for the additional tip! That's good to know!
December 5th, 2008 at 12:03 pm
Hi… I have 5 dates in a row and I want to bring them over to outlook calendar. Is it possible without going to outlook manually
December 5th, 2008 at 3:01 pm
I dont understand your question – can you be more detailed?
December 17th, 2008 at 10:05 am
The website: http://www.wincalendar.com/has been brought to my attention as well. Take a look and I would appreciate feedback if it works for you!
December 18th, 2008 at 11:16 am
Sir,
Microsoft Date and Time Picker Control (SP4) does not appear to be an option in the "More Controls" list on my work computer (Windows XP & Office 2007). Will also try this method using Windows Vista and Office 07 at home. Please adivise and thanks.
December 22nd, 2008 at 2:03 am
cant find the Microsoft Date and Time Picker Control option in your mentioned path!!!! what shall i do???
January 1st, 2009 at 6:58 pm
This is a FANTASTIC tip! Works like a champ. Is there a way to apply this formula to an entire column in a table, so that each row had a calendar date pick? Other than copying/pasting/modifying the properties for each row? Not that I am trying to avoid work…but, as you know, calculated columns allow you to insert rows in a table and the formulas are automatically applied to the new cells. Thanks for any advice!
January 6th, 2009 at 3:32 pm
Download Here: http://danielcurran.com/freeware/MSCOMCT2.cab
To unzip a CAB file look for my posting on 7-Zip
Run the batch file once unzipped
January 7th, 2009 at 8:03 am
Thanks Dan!
I was trying to do this with Office 2007 and Windows vista (business version). The trouble of course was with vista and the security setting it has for activeX. So even though I allowed activeX in excel settings and windows feature settings, it still would not allow me to insert activeX control or view/edit an activeX object in a worksheet.
So I had to use a workaround. Here goes:
In developer mode, insert the activeX control called "Calendar Control 12.0". Then follow the same steps.
This works in vista and win xp too.
January 7th, 2009 at 10:29 am
Outstanding! Thanks for sharing that with everyone else: "In developer mode, insert the activeX control called "Calendar Control 12.0". Then follow the same steps."
January 20th, 2009 at 7:57 am
I already downloaded the MSCOMCT2 folder and the 7-Zip but I don't know how to install the control itself. HELP!!
January 20th, 2009 at 2:05 pm
This has your answer! http://danielcurran.com/instructions/how-to-insta…
January 21st, 2009 at 2:40 pm
How can I use more than 1 Drop Down Calendar on the same spreadsheet? I was trying but I couldn't make it. Thank you!
January 21st, 2009 at 3:40 pm
Look in the properties of the control and make sure each one has a unique name. Using multiple shouldn't be that difficult. If you can describe the exact problem you are having in detail that is a lot more helpful to troubleshoot it.
February 18th, 2009 at 3:57 pm
Anyway I can do the drop-down calendar with Excel 2003??
March 10th, 2009 at 1:38 am
This help me a lot! Many thanks! Any tip regarding the question above?
March 20th, 2009 at 11:11 am
This tutorial has been a fantastic help! However i have now run into a separate issue. The drop down calendars i have created continue to lose their formatting, whenever i close the document. When i re-open the document i am forced to go back into design mode, and resize the objects. Any ideas on how to fix this? I'm creating a form for use within our small office, and it must be completey user friendly. Thanks so much!
March 29th, 2009 at 9:51 am
I believe I'm having roughly the same issue at IT Gurl, when I try to reopen the file after a few days of no use, the drop down calender(s) turns into a giant red X from which I can't choose anything. It's doing this on every page of the worksheet. Any ideas on how to fix this issue? Thanks for any help!
April 29th, 2009 at 7:05 am
This is great, butI also have a related question – how do I have excel data displayed in a calendar form? It works importing data into Outlook, but it is not very functional so I was thinking about using Excel for the calendar but I do not know how to do it.
For example – if I have a project that requires tracking of mulitple records I would like to visually see the schedule and progress. I have multiple columns of dates with associated tasks that I'd like to publish so people know what to do when. Can't use MS Project cause not enough people have it.
Thanks for any help!!!
April 29th, 2009 at 11:07 am
Tom3 I believe the WINcalendar mentioned above : http://www.wincalendar.com can help with what you are looking for
April 30th, 2009 at 11:05 am
I have a list of 8 cells that I want to insert a drop down calendar. I don't want the current date to show up in every cell. How do I not get the current date to show up in the cell and have it just be blank but be able to have a drop down calendar option?
June 9th, 2009 at 2:57 am
As per IT Gurl, the rectangular shape of the object that I originally draw changes after re-opening the file to a square. If I go back into design mode to re-set the shape, the same thing happens again.
How can I fix and lock the shape of the object so that it dose'nt keep resizing?
September 17th, 2009 at 5:58 pm
Many thanks I been loking for this for a long time. But with the difference that i am using it with a userform in VBA. Many thanks
October 27th, 2009 at 11:45 pm
hi, thanks for the great help.
i have a questions. i need to protect the sheet and still people can select the date from the calender. it doesn't work even if i change the locked status in the properties to false?
please help
October 28th, 2009 at 9:44 am
after I select the Microsoft date and time pick and save and click and hold i get a error message
Can not insert object?
November 25th, 2009 at 3:40 am
Can't Find date and time picker in more controles
January 14th, 2010 at 10:04 am
Me to need the same thing need it to fill all da way down!
January 14th, 2010 at 5:52 pm
Flawless. Thank you. Worked great with Excel 2007.
January 29th, 2010 at 5:30 pm
I WANT TO DO THE SAME THING, A CALENDAR IN EACH CELL FOR THAT COLUMN. I TRIED WORKING IT OUT THROUGH THE DATA VALIDATION WAY BUT GOT NOWHERE. IT JUST SPAZZED OUT. NO SIR, DON'T WANNA BE A PROGRAMMER JUST ENJOY THE BENEFITS. FOR THE LOVE OF SOFTWARE, HELP.
February 2nd, 2010 at 1:46 pm
I am having a problem although not with the functionality. I save my workbook as .xlsm. When I reopen the workbook, it prompts me to enabled the content which I do enable. After it is enabled, my date picker controls are displaying in a very odd manner (larger). When I scroll down in the spreadsheet then scroll back up they appear normal sized again. These date pickers control criteria within a query, and once I get this finished, I am going to distribute this to users. I would like the date picker controls to appear correctly. What could be going wrong? How could I fix it?
February 2nd, 2010 at 1:49 pm
Make sure you are looking for "Microsoft Date and Time Picker…" These are listed in alphabetical order. Within the Developer tab on the ribbon, click Insert, then click the button right icon of the hammer and wrench (More Controls), and the date picker should appear in the list.
February 3rd, 2010 at 9:44 am
I am having an odd issue in Excel 2003. I've successfully placed and formatted dtpicker. However, I save and close the document, and when I reopen one of the dates has resized and cannot be clicked on. When I scroll in the document, the problem goes away. Any solution to this bug so that I can open the document and not have to scroll to fix it?
February 4th, 2010 at 12:58 pm
Gurari, I just asked the same question and haven't been able to find a solution. I also posted this in an Excel forum and still no usable help. I'll let you know if I discover anything. Please let me know if you find out the issue as well.
March 15th, 2010 at 1:07 am
Thanks Daniel for a great tip. I experienced the same problems as Brian and Gurari above. I got around it by writing a macro that scrolls the page up and down everytime the workbook opens. As I just dable in macros a little, its probably a very pedestrian approach and there may be something better out there, but for now it works for me.
March 21st, 2010 at 5:30 am
I am Happy, just got what i was searching.
GOOD MAN.
March 29th, 2010 at 6:05 pm
Very useful. However, Microsoft Date and Time Picker Control not among "More Control List"Btw Im using Microsoft Offiice 2007. HELP!!!!!!!!!!!!!!!!!!!
June 1st, 2010 at 5:28 pm
For those having problems with the datepicker resizing itself and becoming unclickable, I use VB code in the workbook to zoom out then zoom back in the window. This seems to be a temp fix for the resizing issue.
To do this hold down ALT + F11, then double click on "This Workbook" found on left hand side, and it will show code on the right hand side: "Private Sub Workbook_Open()" under the following code:
ActiveWindow.Zoom = 100
Range ("A1:M3").Select
ActiveWindow.Zoom = True
ActiveWindow.scrollRow = 1
In the range section enter what cells you wish it to zoom too. All this is doing, is forcing a resizing of the window. Hope this helps.
June 2nd, 2010 at 1:57 am
Thanks a lot Dan but i still have one question, i dont want the dropdown to display a single date but the dates for the whole month (in a cell)
i guess what i am trying to say is the moment i select a month i want my spreadsheet to populate all the 30 days of the month in a column/cell
can this be done
July 22nd, 2010 at 9:38 am
Followed Sean's instructions and it works except when reopening the doc it shrunk so I always had to resize. Made a minor mod and now seems to work fine
:
ActiveWindow.Zoom = 100
Range ("A1:M3").Select
ActiveWindow.Zoom = True
ActiveWindow.scrollRow = 1
ActiveWindow.Zoom = 100
July 29th, 2010 at 6:17 am
Brian, To prevent the "Enable Macros" prompt, click the Office Pearl (as Daniel called it) and select Excel Options-Trust Center-Trust Center Settings-Trusted Locations and add the directory where your Excel book is stored. Check the box to include subdirectories. It's not a great solution, but is is a solution. (Office 2007). The other phenomenon appears to be a bug. I get it too.
September 1st, 2010 at 12:12 pm
How do you 'run' the batch file?
September 1st, 2010 at 7:53 pm
Thanks Daniel…this is a great help for Excel users
September 25th, 2010 at 11:53 am
Thanks
October 1st, 2010 at 10:17 am
Help!! I am using Office 2003 and I have the DatePicker option under the Control Toolbox, and I can do all the steps, but the pull-down never works. When I go to click on it, I get the Cross Hairs like I am editing the Date Control Box I just created. Anyone have any ideas?
Mike
October 3rd, 2010 at 7:50 pm
Great tut, Daniel. I tried the instruction to the T and well, I managed to get the drop down alright. But it would be very very cool to have a sort of "drop when cell is clicked" kind of calender for all cells. I think thats when I have to deal with all those codes, right? Anyways, thanks!
BTW: the download link is not working
October 13th, 2010 at 9:13 pm
Dear All, when i enter the data and time dropdown object, the sheet get errors while opening
It creates an error while sheet is open saying that object printer settings1.bin have been removed while recovery. Have any ideas on how to solve this problem???
November 4th, 2010 at 1:06 am
hi i want to create a pull down date menu that works like this; if i select a date it shows that date in the spreadsheet
November 4th, 2010 at 8:05 pm
Thanks for the tip! This is really useful!
Brian & Guari, that happened to me too. But i figured out that you just need to change the security settings to allow macros again. Then, it will work fine.
November 10th, 2010 at 3:33 pm
Hello Dan,
I would like to have one calender on the sheet to pick the dates and in each individual cell, the date selected from the calendar only relates to the one cell.
I am trying to do up a mtg schedule and require a different date in each row and am trying to make it simple to use. Understand that you can write a macro for this but i would like to avoid it if possible.
Looking forward to your response – let me know if you need more info.
November 29th, 2010 at 6:05 pm
So, download cab file and extract ocx file to c:windowssysWOW64; then programs -> accessories -> command, right click, runas admin. In dos window, regsvr32 c:windowssysWOW64mscomct2.ocx. It works as detailed above for adding and manipulating the control. Only problem is, you can't use it to update individual cells; which is what I was looking for. Back to the drawing board.
December 2nd, 2010 at 10:33 am
Thanks Daniel, i tried the 'WinCalendar' Excel calendar & it works PERFECT. Plus it is free
See here: http://www.wincalendar.com/excel-pop-up-calendar….
December 2nd, 2010 at 3:55 pm
Cynthia, will you share that macro?
Thanks!
December 24th, 2010 at 10:57 am
Wincalender is a fantastic tool to use with Excel thank you for the suggestion.
December 28th, 2010 at 9:00 am
It worked great.
Thx 4 sharing da knowledge!
December 28th, 2010 at 9:40 pm
Thank you very much Daniel for the great tip explained neatly.
January 19th, 2011 at 11:31 am
very cool! Question…
can the properties make it so the cell is blank until a date is manually selected from the calendar?
January 24th, 2011 at 5:46 am
Thanks Daniel,
This is a neat tip.
January 26th, 2011 at 9:39 pm
How do I change the date display sequence to DD/MM/YYYY?
February 3rd, 2011 at 5:32 am
Could you please tell me if there is a way to have the cell appear blank until you pick a date? Right now there alway has to be a date there.
February 12th, 2011 at 9:21 pm
Hi i am not able to get the calendar once i save and re-open it i get only a red color X symbol in the respective cell pls give me a solution.
February 17th, 2011 at 9:24 pm
Hi Really worked for me and it's easier too. I just loved the way it explains for a layman in excel.
February 17th, 2011 at 9:27 pm
How can i just creat an option of Time in "0800 – 0830" format. Would it be possible from same steps???????? Could you help me out in this also???? Thanks Daniel
March 9th, 2011 at 4:14 pm
I am still newbie for Excel but thanks for this tip…
March 19th, 2011 at 5:44 pm
Hey! This works in Excel 2010.
Plus I found out that I could 'copy and paste' the control box if I need multiple on a sheet. Afterwards, I just modify the linked cell for each one.
Neat.
Thanks bro
April 3rd, 2011 at 10:36 pm
Amazing stuff! Going to require a bit of time to think over the points=D
April 6th, 2011 at 5:27 am
Yeah I need the exact same thing, anybody that has found a solution please update us, thanks
April 26th, 2011 at 10:00 pm
Good tip. Anyone got any ideas how to validate i.e. in comparison with date in another cell rather than an absolute date?
Using method linked cell can be changed without validation working
May 6th, 2011 at 5:08 pm
I think the link is broken. Can someone please share any other link?
May 31st, 2011 at 1:39 am
Hi Daniel,
Thanks for the great tip, can you also help me if the format of the linked cell can be Long date too ex: 9 June 2011,
June 30th, 2011 at 12:23 am
Snow boots UGG Boots is full of legend. Seemingly simple-minded but popular UGG Boots UK cartoon form the Eurasian land, while popular in the world the wind blowing. Once you wear UGG Classic Tall Boots, you will not want to take off Tall UGG Boots. Don't miss out, grab a bargain now! Check it Out!
July 28th, 2011 at 5:22 pm
In Excel 2003, I can find "Calendar Control 12.0" either.
July 29th, 2011 at 3:40 am
That's a good solution as long as you are not going to convert to Office 2010. The "Calendar Control 12.0" aka mscal.ocx will be deprecated in 2010 (that's Microsoft-speak for "gone").
August 2nd, 2011 at 8:10 pm
Can you suggest how to get this feature working in the automatic data form so computer illiterate operators can select a date?
August 4th, 2011 at 5:35 pm
Saved the day.
Thanks
August 10th, 2011 at 7:22 pm
till now it is ok that is crating calendar using activex but we are not able to link it to the other sheets when we are coping and pasting to other sheet it is not working can u please let me know how to solve this problem
August 21st, 2011 at 10:46 pm
Thanks for your tips. Please note it is working well in windows 2003 system and windows xp. but when use the same function in windows 7 after installing .ocx file it show cross simple. Please help me..
August 24th, 2011 at 9:17 pm
Whenever I am going to insert the box, an error msg appears “Cannot insert object”. Please help!!!
August 29th, 2011 at 3:45 am
I am running Excel 2010 but I can not see the date and time picker in the cotrol box, under … mroe cotrols.
Thanks
September 6th, 2011 at 3:41 am
Excellent Post. Worked fine in XL 07'. I ended up resizing the date box so all i see is the drop down arrow. I then placed that arrow just to the right of the linked cell. Now it works both ways; i can type a date in the linked cell or i can select the drop down arrow next to the linked cell to select the date instead.
September 19th, 2011 at 7:53 am
Awesome! It helped me alot saved me soo much time! Very good step by step explanation!
September 19th, 2011 at 7:53 am
THANKS!
September 26th, 2011 at 9:06 am
to add a calendar to alldeadsea website, would I use css?
September 29th, 2011 at 7:17 am
Daniel, I would like to create an email alert to notify me that an officer has 90 days until they are due for pistol training and shotgun training. I will be using my Outlook email account to receive this alert. How do I do this?
September 29th, 2011 at 7:19 am
By the way I'm using Excel 2007 for the shotgun and pistol training.
October 4th, 2011 at 4:35 am
When I drew the rectangle, excell gives me an error”Cannot insert object”.It doesn’t give me a date box. Please help
October 4th, 2011 at 10:39 pm
i'm as giddy as a little girl….
Thanks!
October 6th, 2011 at 6:45 am
Thank you very much.. This is just what I was looking for !!!
October 11th, 2011 at 3:56 am
Freakin’ FANTASTIC ! Just what I needed to work round my Access problem.
November 10th, 2011 at 12:22 pm
Thank you – that was awesome!!!!!!
November 11th, 2011 at 4:53 am
Daniel, it really is "Holy Cow that’s super cool awesome" as you yourself said…! Thanks a lot bro…!
November 22nd, 2011 at 2:35 pm
Does this work on a Userform? Its not on the standard control list when creating a userform. Is there a way to add it?
November 22nd, 2011 at 5:31 pm
At last an explaination I can understand.
One question, how to make it default to the current day?
December 25th, 2011 at 12:26 am
Thank you. this is good learning stuff
January 4th, 2012 at 11:46 pm
Thanks. it was new information for me.
January 9th, 2012 at 12:24 am
i like http://danielcurran.com webvisetiy
January 16th, 2012 at 9:45 pm
in 2010, as in my case, you click excel option > customize ribbon, you will see two panels show up, on the right panel, under main tab, you will see the developer tab unchecked, go ahead and check it and you will get it
January 16th, 2012 at 9:46 pm
Thank you so much! That was easy!
January 18th, 2012 at 2:31 am
I am not able to find the Microsoft Date and Time Picker Control option in the said path…please sugest if any addin need to execute..
January 26th, 2012 at 6:12 pm
Hello ,i cant download Microsoft Date and Time Picker Control 6.0 (SP4) into my excel ,kindly any help thanks in advance
January 26th, 2012 at 6:34 pm
Many thanks Daniel! This is a huge help for those of us FINALLY making the leap to Excel 2010, and having to wean off the old Access calendar control.
February 3rd, 2012 at 5:25 am
good article. thanks
February 3rd, 2012 at 5:25 am
good article. thanks.
February 4th, 2012 at 2:24 pm
Did you ever get a response or a solution for this? I am having the exact same issue!
February 4th, 2012 at 2:26 pm
Did you ever get a response or a solution to this? I am having the same problem!
February 4th, 2012 at 2:27 pm
Did you ever get a response to this or a solution? I am having the same problem!
February 4th, 2012 at 2:31 pm
Brian, did you get a response that solved your issue of the DTP control format and the need to scroll the page to make it appear correctly? I ma having the same issue and it wasn't until this post that I found the scrolling workaround. I was hoping that there was a solution to this issue!
Thanks in advance for any assistance provided!
February 4th, 2012 at 2:33 pm
Gurari, did you ever receive a response or a solution to the issue you have posted? I am having the same issue and am hoping for a resolution!
Thanks in advance for any assistance provided!
February 4th, 2012 at 3:03 pm
Cynthia, I am having the same issue with regards to the problem that Brian & Gurari were having. Have you found a solution other than your macro to this issue. If not, would you be willing to share your macro with me?
Thank you in advance for any assistance provided!
February 4th, 2012 at 3:36 pm
Sean, I tried your workaround but I'm not sure if I put it in correctly: I put it in as follows:
Private Sub Workbook_Open()
ActiveWindow.Zoom = 100
Range("A1:T24").Select
ActiveWindow.Zoom = True
ActiveWindow.ScrollRow = 1
End Sub
I am running Excel 2010 if this matters. It doesn't seem to work as well as you described. Not sure if a permanent solution has been found to this dilemma either.
Thank you in advance for any assistance provided!
February 4th, 2012 at 3:41 pm
Noemi,
I applied your code in to my VB window just as you showed but it didn't provide any different result. Do I have to place a "Private Sub…" line code and an "End Sub" line code for this to work?
Thanks in advance for any assistance provided!
February 8th, 2012 at 2:45 am
it is possible when i crate new separate sheet in excel with date and time is automatically add………?
February 8th, 2012 at 2:48 am
it is possible when i crate new separate sheet in excel with date and time is automatically add………?
i mean new insert work sheet…which include date and time in top …..
February 8th, 2012 at 9:24 am
help!!!! I have followed the instructions on the drop down calender, but upon reopening, the calender drop down is duplicated in the upper left corner and won't work in either area until the design mode is toggled. The calender disappears in the corner until design mode is turned off……whats wrong? Excel 2010. By the way, it works fine on a blank worksheet.
February 8th, 2012 at 9:33 am
Another Excel 2010 question. Can I place a image(company logo) on one tab sheet and copy it automatically yo other sheets in the same workbook? The reasoning is to be able to changs images on one sheet and automically update others. Thanks!
February 21st, 2012 at 10:59 am
Could you please tell me if there is a way to have the cell appear blank until you pick a date? Right now there always has to be a date there.
February 29th, 2012 at 6:41 am
Absolutely brilliant – will I tell them the real truth, or bask in the glory…..?
February 29th, 2012 at 7:12 am
Hi,
on the link : http://www.familycomputerclub.com/excel/date-and-…
you can find how to apply this control an entire column, i.e to cell which has focus at this time.
You have to add in VBA code :
Private Sub DTPicker1_Change()
ActiveCell.Value = DTPicker1.Value
End Sub
After that when you open calendar and choice data, data will be placed in cell which has focus.
April 26th, 2012 at 1:47 am
Hi Maen, did u get solution for this.
How to make this calendar enabled when the sheet is protected.
May 2nd, 2012 at 12:02 pm
I don't see Microsoft Date and Time Picker Control in my Toolbox -> more options. I have office 2010. Can you please help?
May 11th, 2012 at 1:23 pm
I found this fix online.
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Add
ActiveWindow.Close
Application.ScreenUpdating = True
End Sub
Paste into "This Workbook" in VBA
June 5th, 2012 at 7:48 pm
I am sure that I saw a pull down menu in a worksheet cell. I want to have a cell formated so that I can have a pull down menu of a calendar in which to choose the date.
July 29th, 2012 at 5:12 am
I have the same issue…
August 10th, 2012 at 8:57 pm
Hi, after adding items in drop down box i want to show some data with the items in drop down box as i select it. Please suggest me something…
September 6th, 2012 at 10:57 pm
Has this one been answered for you? I see the date but it does not appear ont he printed doc.
September 23rd, 2012 at 8:48 am
Hello Daniel I can't find the date and time picker what shall i do
September 23rd, 2012 at 10:30 am
Microsoft Excel 2003
Create a new workbook.
On the Tools menu, point to Macro, and then click Visual Basic Editor.
On the Insert menu, click Module.
Copy the Visual Basic for Applications code below into the module sheet.
On the File menu, click "Close and Return to Microsoft Excel."
Click the Sheet1 tab.
On the Tools menu, point to Macro, and then click Macros.
Click CalendarMaker, and then click Run to create the calendar.
Microsoft Excel 2007 and Excel 2010
Create a new workbook.
On the Developer ribbon, Click Visual Basic.
On the Insert menu, click Module.
Copy the Visual Basic for Applications code below into the module sheet.
On the File menu, click "Close and Return to Microsoft Excel."
Click the Sheet1 tab.
On the Developer ribbon, click Macros
Click CalendarMaker, and then click Run to create the calendar.
NOTE: If the Developer Ribbon is not showing go to Excel Options and enable it. In Excel 2007 you will find it in the Popular menu and in Excel 2010 you will find it in the Customize Ribbon menu.
September 26th, 2012 at 11:50 am
I looked at Wincalendar to see if it is easier that this ingenious workaround — and it is. You're a man after my own heart and I dearly love figuring out things like this, but Wincalendar works fairly well, from what I can see after 10 minutes.
October 12th, 2012 at 12:05 am
Thanks Daniel.
October 15th, 2012 at 5:31 am
Hi, I am using Excel 2007 and want to do a VERY similar action but not exactly like what is described above. I would like to have a calendar pop up whenever I choose a cell within a given column the calendar is invisible all other times. I would like to choose a date via the calendar and have that date visible in the selected (active) cell. If anyone can help with this it would be greatly appreciated. Thank you!
November 27th, 2012 at 2:19 am
will i get whole excel text book in online
December 22nd, 2012 at 7:25 am
Awesome..! Thank you so much..!
December 25th, 2012 at 1:59 am
I find a problem sir
……the control always returns MM/dd/yyyy but i need dd/MM/yy can you solve plz?
January 7th, 2013 at 11:14 am
Thanks a million. That did the trick…
January 8th, 2013 at 5:56 am
Worked just like it says on the box. Fantastic!!
My only question now has to do with the property settings. I have managed to change font size type, Calender colours etc but can't seem centre the calender date in my created rectangle. Any hints?
January 22nd, 2013 at 1:16 pm
Thanks Sean,
This fixed the issue for me as well. Was banging my head trying to get this to work. DTPicker always showed up in Upper Corrner and Cell A title. This forced scroll was a great catch.
Thanks Joni
January 30th, 2013 at 1:40 am
Have you got the solutions? I am facing a same problem too. could you share to me please
March 4th, 2013 at 10:40 am
Is their a way to calculate number of days between two calendar dates when you are using the datepicker function?
April 22nd, 2013 at 11:50 pm
Great thanks i shall try that!
May 1st, 2013 at 1:32 pm
Usually you just have to unlock the linked cell. This is how to ensure combo boxes and drop-downs work on protected worksheets/books. This is a very late reply but I only just got here!!
May 2nd, 2013 at 5:52 am
This worked great, but now it's freezing up my worksheet.
Please tell me how to REMOVE it. I can't seem to figure it out.
Any help would be appreciated!
Many thanks!
May 8th, 2013 at 11:30 am
Thanks for the excellent work around. Another easy way of adding a drop down menu for dates in a cell falling within a year is to
1. Add a list of all the dates of a month/s in a column.
2. Then click on the cell you want to enter a date.
3. Then go to Data tab> Data Validation > under Settings tab, in the Validation Criteria title, choose list from the drop down menu. Keep 'Ignore blank' and 'In-cell drop' down checked.
4. Click on 'Source' field and select the cells containing the date range you had made in the beginning (Step 1)
5. Click OK
Now when you click on the cell you want to enter the date, a drop-down arrow appears which will allow you to select the date of your choice from the the range you had created.
May 15th, 2013 at 10:14 pm
Thanks a lot. This really helped me:-)