#ExcelPedroWave

Control de Calendario Rodante en Excel

Enlace a este artículo en Español

New Calendar Control with dates and times

Usually the calendar controls only allow changing the date, showing it within a monthly calendar, like the one I published 6 years ago and that you can analyze in the following link:

Drop-Down Calendar Control

Now I have decided to create a new Rolling Calendar control with dates and times in Excel hat allows modifying both dates and times, and for this I have designed a shape in Excel, that I have named ControlCal, and that shows both the date and the time in 7 pairs of digits with: the day; the month; the thousands and hundreds of the year; the tens and units of the year; the hours; minutes and seconds, which can be seen in this image:

This Calendar Control consists of 4 groups:

1. Left side with 3 buttons to change the zoom of the Control, in main mode.
2. Left center to change the date.
3. Right center to change the time.
4. Right side with 3 buttons to set the current date and time; for accept changes and to undo changes, in default mode.

Right-clicking on the side buttons more options are shown, which I will explain in a tutorial of this Contol of Calendar in the next article of this blog.

 

Video with the new Calendar Control

It is a rolling Calendar Control because one of its main features is that the figures roll or rotate up or down to change its value.

In the following video, made with the first version of the Control, you can see the new Calendar Control in action:

ATTENTION: This Control Calendar create a 'ControlCal' sheet in each book that is enabled to display it, in which the Control data is updated, and which is displayed by a shape created with the Excel Camera (watch usage example here) on each sheet that the Control will use.

This is the sheet that serves as the basis to make this Control of Calendar:

This sheet will be explained in a future tutorial on this blog.

Cells with date and time formats

To display this Calendar Control, the format of the cells must be of a number type with date and / or time format. In the next image shows several valid formats:

In the next article of this blog I will give more details about the formats of valid cell numbers for this Calendar Control.

Calendar Control Download

Download this new Calendar Control from Google (with the button "Excel Download") or from the link to Microsoft OneDrive:

Opens the file, allows editing, and enables the content and macros to test this unique Calendar Control, which I have dared to design when not finding any similar on the Internet.

If you know of someone who has published a similar Calendar Control in Excel, please! I would like to know!

Copyright

I am publishing under a Creative Commons License: 

The license terms are:

• Attribution: Give appropriate credit, especially keep my name and my blog name in the workbook and the code.
• Share the same : Give it away for free. I'm giving it away for free as well which also has to do the same.
• Noncommercial : Don't use my template and code to create a template or workbook for business sale.

Calendar Control Add-In

To use this Calendar Control in all your templates, you must install it as an Excel add-in.

This page explains how to add or remove add-ins from Excel:

Add or remove add-ins in Excel

To install the downloaded file as a add-in:

1. Open the downloaded file
2. In the menu select: Archive
3. Save as
4. This PC (See image below)
5. Select from the dropdown: Excel Add-in (.xlam). (See image below)
6. Click the button: Save
7. Close the downloaded file

To activate the add-in:

1. Open Excel
2. Open a new workbook
3. In the menu select: File
4. More...
5. Options
6. Add-Ins
7. Under Manage, select: Excel Add-ins
8. Click on the button: Go...
9. Check the new add-in: Control Calendario Rodante
10. Click the button: OK

This is how the new add-in available should be checked: Control Calendario Rodante

To display the new Calendar Control:

1. Write a date in a cell
2. Show the context menu with the right mouse button
3. If the button above is not Control Cal, select another cell and press this 3 keys at the same time: Control + Shift + R
4. Show context menu with right mouse button
5. Click on the button above: Control Cal
6. The question appears: Do you want to create the Control?
7. Click the button: Yes

The first icon in the context menu is the Control Cal, as shown see in this image:

Tutorial for the use and design of this Control

In two next articles I will give a tutorial with the instructions for use and how this new Rolling Calendar Control was designed with dates and hours, to which I have devoted many hours of development.

I hope that the use of this Control is a comfortable, useful and innovative way to enter dates and times in Excel cells, for which I have Tried to make it compatible with all versions of Excel, from Excel 2010 through Excel 365, both in 32-bit and 64-bit versions.

If you find it interesting, give it a try and give me suggestions and comments for be able to debug it and improve it in future versions.