?
Solved

How do I create a pop up calendar in Excel?

Posted on 2014-02-26
9
Medium Priority
?
1,338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 23

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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:5itface
ID: 39890325
got some errors when I launched the Excel sheet you modified. See attached.
Capture.JPG
Capture1.JPG
0
 
LVL 23

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 23

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 23

Accepted Solution

by:
Ejgil Hedegaard earned 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

765 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