Excel Year Planner Maker

22 November 2017: Hi folks! Thanks to everyone who's downloaded and enjoyed my year planner macro over the years. I have had reports that the macro does not work under Office 2016 which until now I have not been able to test. As I write, the macro (V4.1) works unchanged on a new Office 365 install on a Mac. However if you have another workbook open it will fail with a 'Subscript out of range' error. I promise to fix this in the near future! So, close all other open workbooks and try again.

{Now Mac compatible. Finally.}

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

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
November 2017 Upadate:The macro runs on any Mac running MacOS High Sierra and Office 365. It is still a bit slow on a Mac. Sorry.

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

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

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

A summary of what Year Planner Maker can do:-

• 2018, 2019... in fact any year
• Split years - start the chart on any month, for 12 months eg. June 2018 through to May 2019 - 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 printer? 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.
Enjoy.