Solved

How do I create a pop up calendar in Excel?

Posted on 2014-02-26
9
1,229 Views
Last Modified: 2014-03-19
I have a template attached that I would like to include a pop-up calendar. In column F and G, I'd like to have a pop up calendar feature to where it pops up when I click on the cell in those columns to allow me to select a date. Also, I like a drop down feature in column K, L, and M to allow me to select a number from 0-200. Something like a drop down list. If someone can help modify this list, and send it back to me, I will give max points. Thank you.

My version of Excel is 2010.
Patch-master-list.xlsx
0
Comment
Question by:5itface
  • 4
  • 4
9 Comments
 
LVL 19

Expert Comment

by:helpfinder
ID: 39889426
for pop up calendar you will need implement VBA (macro) if it is possible for your kind of use because it is no t acommon Excel feature.
Regarding drop-down menu in K,L,M you can use Data>Data validation feature (I did from 1 to 10)
Patch-master-list.xlsx
0
 

Author Comment

by:5itface
ID: 39889453
I have VA macro enabled. Can you help me edit my template and send it back to me? Thank you.
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39889920
See file with a date picker in a userform.
The userform will show when a cell in the range F5:G6 is selected, approximately at the selected cell.
The range is named DateRange.
Expand when more rows are added.

Have expanded the number pick datavalidation in K, L, M to be 0-200, using a named range (NumberSelect) on Sheet2.
Patch-master-list-2.xlsm
0
 

Author Comment

by:5itface
ID: 39890325
got some errors when I launched the Excel sheet you modified. See attached.
Capture.JPG
Capture1.JPG
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39890554
The first error Capture.jpg is because the Date picker control can not be found.
In the picture is the references I have active for this file.

Go to VBA editor, menu Tools-References
I think "Microsoft Windows Common Controls-2 6.0 (SP6)" is missing.
Find it on the list and mark it.
Then compile to check for errors.

If that does not help.
I have seen errors with this library mscomct2.ocx, occurring when Microsoft send auto updates for Windows, that reference in the registry is broken, and something that has worked, suddenly does not.
Go to the Start button on the lower left of the screen, select Run and type
regsvr32 /u mscomct2.ocx
You get an info that the change is successful.
Then connect again with
regsvr32 mscomct2.ocx
Again a confirmation pops up.


The second error Capture1.jpg, I don't understand.
It is a reference to the named range DateRange on the sheet.
How can a range name disappear!
Go to the Formula tab and check the named ranges.
Or replace [DateRange] with ActiveSheet.Range("F5:G6")) to see if it works then.
Be aware that the reference is static, and don't change with changes on the sheet, where reference to a range name is flexible.
References.gif
0
 

Author Comment

by:5itface
ID: 39892447
Here is what I got when I tried the steps above (attached screenshot). Would it be easier if you just take what I have, and help me create a template from scratch?
Untitled.png
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 39892690
Since mscomct2.ocx is not on your machine, you have to download and install it.

Find your path for the similar file mscomctl.ocx to know where to install it.
For me (XP) it is windows\system32

Google mscomct2.ocx, and find the microsoft download site for it.
0
 

Author Comment

by:5itface
ID: 39896929
Would be easier if I can have a template of anything excel with that date picker, so I wouldn't have to do all this. Do you know where I can find an excel template with this feature? I can just take it and add and edit what I need to it. Thanks.
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 39897177
If you don't have access to the Date picker control specifications, = the file mscomct2.ocx, then it is not possible to use it, no matter what you do.
It has to be installed.

Another approach is to make a simple data validation with a list of dates to select from.
Not as flexible as a real date picker, but it will work without any VBA code, and thus good for files distributed.

In attached file is a date list on Sheet2, with dates in the range today +/- 30.
I have set the validation rules to give a warning, if a date not on the list is typed, so any date can be accepted.
Patch-master-list-3.xlsx
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now