Saturday, December 22, 2007

Excel Year Planner Maker

{Now Mac compatible. Finally.}

New! Yearplanner Maker 2013 Edition now available! See below!

New! 2013 PDF versions!

New! Video demo!

Here you'll find my solution to a simple problem - how to use Excel to create wall planner charts automatically.

Previously, I had to draw them by hand in Excel - filling, shading, bordering, numbering, all done manually. Any changes to formatting was time consuming and repetitive.

So I set about writing some VBA code to create an app to do the job for me - to turn out presentable Sasco style yearplanners in a variety of formats at the click of a mouse. All you need to do is tell it what year it is and the month you'd prefer the chart to begin on, and the rest is taken care of. 

The result is my Year Planner Maker, a free, perpetual chart creator that allows you to tweak away until you get the look you want.

The images below are of the latest version.

A few words about security
Above all else, your security on line is paramount. Unfortunately, because my macro is unsigned, you need to turn off Excels' default macro security to run it, if you have not done so already. This process varies between versions, so I've outlined the process for Excel versions 2000 and 2007.

Excel 2007 & later: First close the Yearplanner workbook if you have it open. Click the Office button, then click the Excel Options button that appears at the bottom of the pane. Click on Trust Center, then Trust Center Settings.

Choose Macro Settings, then choose to enable all macros. If macro security is a concern to you, you’ll need to repeat these steps to enable it after using my app. Lucky for you Microsoft made these controls so easily accessible. /sarcasm

Excel 2003 and earlier: First close the Yearplanner workbook if you have it open. On a blank workbook, choose Tools>Macros>Security then select Low.

Excel:Mac 2011: Choose Excel>Preferences>Security>Macro Security. Uncheck the box.

The macro performs no file operations, but any concerns can be put to me through the comments or directly via Twitter.

Mac compatibility
The macro works with Macs provided you have Office 2011 installed.  Kudos goes to Microsoft for making this happen. There are a couple of issues however. One problem is how the Control Panel dialog is displayed, insofar as it's functional but the layout is broken by the different sized OS X on screen controls. The other issue is that of speed. On Windows, the chart 'draws' more or less instantly; on OS X it takes 30 seconds or more. It hasn't crashed. It's just taking it's time. Otherwise, the macro produces exactly the same results on OS X as it does on Windows. Enjoy.

Update October 2012: Version 4.1 - Added second colour palette option for split years.

Update January 2012: Version 4 - Now able to start the year on any month; cleaned up the Control Panel; colour swatches automatically filled when Random colours chosen; planner start point determined automatically.

Update September 2011: Below are links to four sample charts for 2012 in PDF format.

2013-2014 Split Planner - Vertical - Colour

For further customisation options, download the Excel workbook, link below.

Download the Yearplanner Maker 2013 Edition (V4.1 - hosted by 

A summary of what Year Planner Maker can do:-
  • 2012, 2013... in fact any year
  • Split years - start the chart on any month, for 12 months eg. June 2012 through to May 2013 - ideal for academic planners (see picture below)
  • Charts of any colour or black and white
  • Colour Randomiser!
  • Shaded days
  • Alternately coloured weekday
  • Week Numbers
  • Grid lines
  • Text entries from a dated list (UK Holidays for example, included)
  • Pick out selected dates in a different colour with Event and Highlight function
One advantage of Excel is that the result is just a standard spreadsheet. It can be edited beyond what the app achieves, to suit your requirements. 

Got access to a large format plotter such as an HP Designjet? Excel can easily print your planner up to A0 for a true wall planner effect. (See image below)

Horizontal Layout

Vertical Layout showing split year

Printing A0 size on an HP Designjet

A brief guide to using the macro

Control Panel Options

The control panel is self explanatory, but there are basically two modes of operation to bear in mind.

First, changing the colours will immediately affect the look of the chart in the background. This is because the macro dynamically alters the Excel palette – no need to redraw the whole thing. This instant feedback is really handy for fine-tuning the shades on your chart.

If you change any other setting, you will need to press the Preview button to see the result because of the need to redraw the chart from scratch.

So feel free to play around with colours and options to get the chart you want – the settings are preserved so that you can save the workbook and return to it later. Just remember to click Start on the Start sheet to return to the Control Panel.
Feedback, suggestions and bugs can be reported using the comments.

The video. If you can be bothered listening to me droning on for 10 minutes, you'll see how quickly and easily you can create custom planners. I did run into some palette problems on my old copy of Excel, but the demo is at least honest enough to admit the macro isn't perfect.