[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1372
  • Last Modified:

How do I create a pop up calendar in Excel?

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
5itface
Asked:
5itface
  • 4
  • 4
1 Solution
 
helpfinderCommented:
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
 
5itfaceAuthor Commented:
I have VA macro enabled. Can you help me edit my template and send it back to me? Thank you.
0
 
Ejgil HedegaardCommented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
5itfaceAuthor Commented:
got some errors when I launched the Excel sheet you modified. See attached.
Capture.JPG
Capture1.JPG
0
 
Ejgil HedegaardCommented:
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
 
5itfaceAuthor Commented:
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
 
Ejgil HedegaardCommented:
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
 
5itfaceAuthor Commented:
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
 
Ejgil HedegaardCommented:
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now