A Blinking Excel Christmas Tree

Happy Christmas and New Year

I share with you a blinking Excel Christmas Tree (made using the iterative calculation) to celebrate the upcoming New Year holiday.

To see the Christmas Tree, change the AZ1 cell value.

Excel2015_PW1.xlsx 

Christmas trees without the use of iterative calculation

Some Excel guys have done Christmas Trees with VBA macros or with formulas, for which users need to press repetitively the F9 key to recalculate the worksheet or users could slide a scroll bar to have a lively tree.

The Excellent blogger Debra Dalgleish shared in her forum site (Contextures Excel Blog) a blinking Christmas tree that uses VBA code to run, so you will need to enable macros for the workbook. To start or stop the tree blinking, right click anywhere on the worksheet. Here are some examples:

A Blinking Christmas Tree

Excel Scroll Bar Christmas Tree Example

Excellent Christmas 2009

Iterative Christmas Tree

My challenge is: How to do this without VBA/macros, scroll bar or F9 key?

I solved this challenge using the iterative calculation.

How to use Excel iterative calculation?

1. Click the File tab, click Options, and then click the Formulas category.
2. In the Calculation options section, select the Enable iterative calculation check box.
3. Set the maximum number of times Excel will recalculate, type the number of iterations in the Maximum Iterations box to 1,199.
4. To set the maximum amount of change you will accept between recalculation results, type the amount in the Maximum Change box to 1.

This Excel tree is made with 4 sheets, 3 of them very hidden so the user cannot make the sheet visible through the Excel user interface.

To see all sheets, press these two keys together: Alt - F11 to see the VBA Editor, see the sheet properties and change each Visible propertie from xlSheetVeryHidden to xlSheetVisible.

• Sheet "LaVi": Text in Spanish ("FELIZ FORO") and text in English ("HAPPY GUYS")
• Sheet "Vi": Christmas tree.
• Sheet "NoVi": Blinking iterative formulas.
• Sheet "SiViDad": Blinking Christmas Tree.

To see the Christmas Tree into the last sheet, change the value of AZ1 cell to 1.

Christmas Tree Download

On the cloud is not possible to see the tree blinking.  You need to download from here.
(Prueba la descarga desde estos dos enlaces: Google Drive - Microsoft OneDrive)

Excel2015_PW1.xlsx 





Enjoy and happy holidays!

Un Arbol de Navidad Intermitente

Feliz Navidad y Año Nuevo

Hoy comparto contigo un Arbol de Navidad Intermitente en Excel (hecho usando el cálculo iterativo) para celebrar las fiestas de Navidad y Año Nuevo 2015.



Para ver el Arbol de Navidad se debe cambiar el valor de la celda AZ1 a 1
(Descarga desde Google Drive o Microsoft OneDrive)

Excel2015_PW1.xlsx 

Arboles de Navidad que no usan el cálculo iterativo

En Excel se han hecho Arboles de Navidad con macros VBA o con fórmulas, para los que los usuarios necesitan hacer clic repetitivamente en la tecla F9 para recalcular las fórmulas de las hojas o se debe desplazar una barra para tener un árbol animado.

La Excelente bloguera Debra Dalgleish compartió en su foro (Contextures Excel Blog) un árbol de Navidad intermitenete usando código VBA para animarlo, por lo que se necesita que las macros estén habilitadas. Para arrancar o parar el árbol intermitenete, el usuario debe hacer clic con el botón derecho del ratón en cualquier sitio de la hoja. Aquí hay algunos ejemplos:

A Blinking Christmas Tree

Excel Scroll Bar Christmas Tree Example

Excellent Christmas 2009

Arbol de Navidad Iterativo

Mi reto ha sido: ¿Cómo hacerlo sin macros en VBA, barra de desplazamiento o tecla F9?

He resuelto este reto usando el cálculo iterativo.

¿Cómo usar el cálculo iterativo de Excel?

1. Clic en la pestaña Fichero, clic en Opciones y en la categoría Fórmulas.
2. En la sección de Opciones de cálculo, seleccione Habilitar cálculo iterativo.
3. Poner el número de Iteraciones máximas a 1.199.
4. Poner el Cambio máximo a 1.

Este árbol en Excel está formado por 4 hojas, 3 de ellas muy ocultas por lo que el usuario no puede visualizarlo a través de la interfaz de usuario de Excel.

Para ver todas las hojas se deben presionar juntas las dos teclas: Alt - F11 para entrar en el Editor de VBA, ver las propiedades de las hojas y cambiar en cada una de las hojas la propiedad Visible de xlSheetVeryHidden a xlSheetVisible.

• Sheet "LaVi": Texto en español ("FELIZ FORO") y texto en inglés ("HAPPY GUYS")
• Sheet "Vi": Arbol de Navidad.
• Sheet "NoVi": Fórmulas iterativas intermitentes.
• Sheet "SiViDad": Arbol de Navidad Intermitente.

Para ver el Arbol de Navidad en la última hoja, se debe cambiar el valor de la celda AZ1 a 1.

Descarga del Arbol de Navidad

En la nube no es posible ver el árbol intermitente.  Para ello se necesita descargarlo desde aquí.

Excel2015_PW1.xlsx 





¡Disfruta y felices fiestas!

Drop-Down Calendar Control

A new drop-down calendar challenge

This time the challenge has been to create a Drop-Down Calendar without any handle Windows APIs, because in Excel 2010 and 2013 is not available the "Calendar Control" add and I didn't want to enable the previous versions of the calendar control for Microsoft Excel 2010.

You can see in any cell pressing the right mouse button or in the cells of the columns that have "*DATE*" in the first row or, knowing something about VBA macros, selecting the cell's range where will be displayed the drop-down calendar.

This calendar can be customized without knowing about macros or VBA, because the calendar image is obtained from the "CalCon" sheet and dates such as holidays or events are selected in the "CalFechas" sheet.

Download this drop-down calendar control

Link to download the Drop-Down Calendar template from here:

Control de Calendario PW2.xlsm 

Drop-Down Calendar Requirements

The following requirements have been proposed:

1. Programming the Control with minimal use of VBA macros, only to display the Control and to change selected month.
2. Create the Control with only native Excel forms, either images or form controls and ActiveX.
3. Calculate and plot the calendar using Excel formulas.
4. Show the month of the date of the active cell.
5. Controlling the change of dates into protected cells.
6. Easily change the month and year shown.
7. Quickly return to the current day or the day of the active cell.
8. Shadow the selected day just when it is changed the date of the active cell.
9. Display week number starting on Sunday or Monday and according to ISO 8601
10. Change Control zoom. (Buttons: < 0 >)
11. Display the Control around the active cell.
12. To include holiday dates colored and automatically generated, for example How to make the Easter dates.

More Drop-Down Calendar Requirements

18-11-2014: I added some additional features to this calendar control to facilitate its use, that you can see in the animated picture:

1. Mark the days above the mouse cursor is passing over.
2. Check if you mark the days above the cursor is passing. (Button: o)
3. Check that shows everyday. (Button: x)
4. Control the number and type of week: weeks start on Monday or Sunday. (Button: w)
5. Copy control to an existing or new sheet. ("CalCon" sheet button: "Copy the Control shape on a sheet")

Drop-Down Calendar video

In the video you can see how to include the form into any sheet grouped together, whether new or existing, and how to copy the macros of "CalTest" or "CalFechas" sheet to the new sheet where the Calendar will be displayed.

Enable the old Drop Down Calendar

If after knowing my new Calendar control you still want to enable Excel 2007 control in following versions, you can read the next Microsoft support, knowing that 64-bit Windows doesn't work with the old Control: Habilitar Control de calendario y DatePicker para Microsoft Excel 2010