It’s only a week until Christmas Eve, and to help you celebrate the holidays, I have updated my Scroll Bar Christmas tree. No macros are needed, but this version uses customizable icon sets that are available in Excel 2010 and later versions.
This will be my last blog post until the new year, so happy holidays, and remember to cell-ebrate safely!
The Excel Christmas Tree
Here is a screen shot of the updated Christmas Tree workbook.
This version uses the conditional formatting icons from the 2009 version, and the gifts and sequential tree trimming from the 2010 version.
Linked Picture
In the 2015 version, there is a picture of Santa that appears at the end of the sequence. John Walkenbach suggested that in 2009, and better late than never!
This is a linked picture, that refers to the SantaPic named range. That range is an empty cell (J1), until the Spin Button reaches a setting of 59. After that, it shows the picture from cells J4:L12.
=IF(Tree!$G$3>=59,Lists!$J$4:$L$12,Lists!$J$1)
Conditional Formatting Icons
On the tree, the lights, tinsel and star at the top all appear based on conditional formatting settings.
In a merged cell at the top, a Star icon appears if that cell’s formula returns a 1. I don’t usually use merged cells, but will make an exception for Christmas.
=IF($G$3>=VLOOKUP(ROW(),RowLU,3,TRUE),1,0)
Then, using customized icon sets, a value of 1 or greater shows a star, and other values show no cell icon.
Lights and Tinsel
The conditional formatting for the tree lights and tinsel strings works the same way. A lookup table sets the values for each row number, with higher numbers appearing later in the sequence.
=IF(VLOOKUP(ROW(),Lists!$F$2:$G$17,2,TRUE)>$G$3,0,(RAND()*100)+1)
There are 3 possible tree ornaments/lights, and the RAND function is used to mix them up. I add 1 to the result, to ensure that the result is never zero.
=IF(VLOOKUP(ROW(),Lists!$F$2:$G$17,2,TRUE)>$G$3,0,(RAND()*100)+1)
The tinsel strings use the Zero Filled Boxes icon.
Start the Tree Trimming
There is a spin button on the worksheet, and you can click that to move up or down in the tree trimming sequence. The previous version had a scroll bar, but that was acting very flakey (getting stuck, disappearing, etc.), so I got rid of it.
The spin button is linked to cell G3, and you can clear that cell if you want to start over. Or, if you’re impatient, type 60 in that cell, to see the completed tree.
Tree Trimming Messages
Just like the 2010 version, this tree has messages that appear, based on the Spin Button value. You can change the messages, or add new ones, in the table in the workbook.
Download the Christmas Tree 2015 Workbook
To see you the Christmas Tree works, you can download the sample file from my Contextures website. On the Excel Scroll Bar Christmas Tree page, go to the Download section, and get the 2015 version of the file. The zipped file is in xlsx format, and does not contain any macros.
Happy Holidays!
_____________________
