A few years’ ago I created a video showing you how to create an animated Christmas tree. See the video here https://www.bluepecantraining.com/portfolio/created-a-christmas-tree-with-sparkling-lights-in-excel/
It’s Christmas time again and I thought it was time to create version 2.0 to include an automated countdown to Christmas and animated tree and stars, all on a Christmassy background. Watch the video below and read the tutorial with all the formula and VBA code that follows.
You can download the version I created here, but if you want to create your own here are some pointers. If you are going to download the file, I would recommend saving and closing any open workbooks before opening this one.
Creating the Sparkling Christmas Tree
This video will take you through how to create the Christmas tree with sparkling lights.
Adding the Background
On Excel’s Page Layout tab, click the Background button and choose your own background picture.
Adding Sparkling Stars
You can do this using the star icons in conditional formatting. Use this formula in each cell that you want a star in:
=RANDBETWEEEN(0,1).
Then use the rules shown below.
Countdown Text
There are the three formulas for the countdown to Christmas text:
The Countdown Formula
=IF(TODAY()=DATE(YEAR(TODAY()),12,25),”It’s Christmas!”,IFERROR(DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,24),”d”)&” Days “&HOUR(DATE(YEAR(TODAY()),12,25)+TIME(0,0,0)-NOW())&” Hours “&MINUTE(DATE(YEAR(TODAY()),12,25)+TIME(0,0,0)-NOW())&” Minutes “&SECOND(DATE(YEAR(TODAY()),12,25)+TIME(0,0,0)-NOW())&” Seconds”,DATEDIF(TODAY(),DATE(YEAR(TODAY())+1,12,24),”d”)&” Days “&HOUR(DATE(YEAR(TODAY())+1,12,25)+TIME(0,0,0)-NOW())&” Hours “&MINUTE(DATE(YEAR(TODAY())+1,12,25)+TIME(0,0,0)-NOW())&” Minutes “&SECOND(DATE(YEAR(TODAY())+1,12,25)+TIME(0,0,0)-NOW())&” Seconds”))
The “Until Christmas Day …” Formula
=IF(AND(TODAY()>DATE(YEAR(TODAY()),12,25),TODAY()<=DATE(YEAR(TODAY()),12,31)),”Until Christmas Day ” & YEAR(TODAY())+1,IF(TODAY()=DATE(YEAR(TODAY()),12,25),”Happy Christmas”,”Until Christmas Day ” & YEAR(TODAY())))
The “Sleeps Left” Formula
=IF(TODAY()=DATE(YEAR(TODAY()),12,25),”No Sleeps Left”,IFERROR(“(“&DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,24),”d”)+1&” Sleeps)”,”(“&DATEDIF(TODAY(),DATE(YEAR(TODAY())+1,12,24),”d”)+1&” Sleeps)”))
VBA to Automate the Countdown and Animation
I WOULD RECOMMEND CLOSING ANY OTHER WORKBOOKS BEFORE RUNNING THIS CODE.
Enter the following code in the ThisWorkbook module (follow these steps)
- Press ALT F11 to open the Visual Basic Editor (VBE)
2. In the Project Explorer on the left of your screen (CTRL R if it is not visible), double click on ThisWorkbook.
3. Enter the code below in the code window on the right of your screen.
Private Sub Workbook_Open() 'when the workbook opens run the SparkleXmasTree procedure Call SparkleXmasTree With Application 'hide ribbon, formula bar, status bar and set window dimensions .ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" .DisplayFormulaBar = False .DisplayStatusBar = Not Application.DisplayStatusBar .WindowState = xlNormal .Width = 1100 .Height = 650 .Caption = "Merry Christmas From Blue Pecan Computer Training Ltd" End With With ActiveWindow 'hide scrollbars, gridlines and sheet tabs. .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False .DisplayGridlines = False .DisplayWorkbookTabs = False End With End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) 'before the workbook closes make the formula bar visible Application.DisplayFormulaBar = True End Sub
Enter the following code in a standard module. To create a standard module, follow these steps.
- Press ALT F11 to open the Visual Basic Editor (VBE)
2. Make sure the Project Explorer is visible on the left of your screen as below – CTRL R if it is not visible
3. Click Insert | Module
4. In the Project Explorer, double-click on the new module you have created and enter the code in the code window on the right of your screen.
Sub SparkleXmasTree() With Worksheets("Sheet1") 'recalculate worksheet .Calculate 'prevent scrolling .ScrollArea = "A1" 'protect sheet .Protect End With 'reset zoom to 60% if user changes zoom ActiveWindow.Zoom = 60 'run this procedure every second Application.OnTime Now + TimeValue("00:00:01"), "SparkleXmasTree" End Sub
When you save your Excel workbook make sure it is saved as a macro-enabled workbook.