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.

All your base

Ok, let's get one thing clear. I love toys. Not just any old tat, mind. Some toys are just too good for children. One example might be the Lego Bionicle range. Sure, it's a construction toy. It's my favourite (Lego), but it's a sharply designed, fully poseable (very important, this) action figure that really looks the biz. And it's aimed at kids. These things, the Bionicles - and any other Lego stuff for that matter - are well designed and made of the highest quality materials and are possibly just too good to be played with.

I note also that K'nex, that spindly arch rival to Lego, now has a brick-like component in some kits, complete with studs that in no way whatsoever could mate up with, oh, I dunno bricks from some other toy manufacturer. That's right, Megabloks.

So anyway, with toys these days mashed perennially with other media, particularly TV, I took the opportunity to pick up a Supermag Cyberman, modelled after the new Doctor Who characters. He's a jolly little chap, standing about 7 inches/17cm tall.



Plastwood (the makers of the Supermag range) have done a cracking job on the details, particularly the head and torso, using quality plastics with a good fit and finish.

The trademark magnetic joints primarily make up the arms, with the head, torso, hip and ankle joints being steel ball and plastic sockets. Sadly, the knees are fixed - I suspect the intention was to make them magnetic, but the lack of friction to hold these joints in place would have been a deciding factor.

The upper legs are just the Supermag plastic rods and so look spindly in comparison to the rest of the figure.


The Doctor Who range includes a Dalek, K-9, and a Tardis, but I think this is the best because it's the least construction-y toy looking. And he looks cool, to boot.

My definition is this

The other day I visited our local, friendly 'recycling centre' (AKA 'the dump') to drop off some 'materials for recycling' (AKA 'rubbish') only to notice the giant crates of broken CRT TVs sitting there like the sun bleached ivory of a technological graveyard. With a slight twinge of sadness, I wondered (had they not been quite damaged by the October climate in Scotland) if any were perfectly good TVs before they made that last, lonely trip to the telly knackers yard. Requiring only a Playstation 1 or just a set top aerial for company, I suspect many had years of quality viewing left in them. Perhaps, I thought, they had company on that last trip in the shape of a broken Flymo and that Strimmer that never fed it's twine properly. Ever.

It's now quite hard to buy a CRT, standard definition TV here in the UK unless you go to a supermarket and even there they are flogging cheap HD-ready, digital TV capable screens of decent size and questionable quality. I, for one, welcome our new digital TV overlords having 'bought into' the idea last year with the purchase of a modest 26" model from Philips, and only then just managed to clamber halfway from the primordial sludge that is Standard Definition TV.

So earlier this year, in order to lie basking on the sun-cracked mud of HD and with not a little trepidation, I purchased a Sony HDR-HC5 high definition camcorder to replace my broken Canon. Call it futureproofing if you like, but I was determined not to go back to fuzzy old DV, even if I can't fully utilise the HV footage, for reasons I shall explain soon.

Next up will be a mini review of the HDR-HC5 and my thoughts on that choice 6 months on from buying it.

Hey, it's summer! Someone tell God!

It's been a lousy summer so far here in Blighty. Even in Scotland, a country not known for it's balmy evenings, it's mostly been cold, dull and grey. My weather widget seldom brings cheer. Not much by way of opportunity to whip out my new camcorder. Oh yes.

The old one, a Canon of a scant 4 years of age, has a broken imaging sensor - a fault recognised by Canon and fixed free of charge if I can get my lazy arse around to packing it up and shipping the thing off for repair.

So anyway, rather than do that I went, in true gadgeteer style, and got meself a high def unit from Sony. Rather than go nuts and get a hard drive or DVD or even solid state model, this one records onto tape. A wee bit retro, don'tcha think? But for good reason, dear reader. Tape, simply because iMovie HD won't talk anything else. And it's a good archive medium. Although with iLife 08 waiting in the wings at Apple HQ, I wouldn't be surprised if the latest version supported AVHCD, the new-fangled format supported by most non-tape camcorders.

And with HD video chewing through 1 gig of drive space per minute of footage, I can't afford to have anything significant hanging around on the iMac. Once edited down, that holiday footage can be punted back onto tape for archiving and later embarrassment of my daughter when she's 18 (currently 10 months).

I'll post a review of the fancy-schmancy 'corder soon. It's a HDR-HC5 btw, you Sony model number spotting freaks.

Slim fast

I landed a gadget windfall a while back when I exchanged my trusty Sony Ericsson T630 for a Samsung X820. I say windfall, because I got the Samsung for free and got to keep the T630.

Anyway, the Samsung has turned out to be a fine phone. The downside of the 6.9mm casing is of course the battery. Standby time is fine - the screen is off to save juice - but actual usage time is measurably shorter than the T630. Lots of use of the 2M pixel camera or shooting video shortens the battery life dramatically. So much for being on the bleeding edge.

But battery aside, I'm liking the X820. Good call quality, vibrant screen, decent camera and inuitive interface all lead to a pleasant phone experience. It even has some decent ringtones built in, something the T630 didn't.

Oh, one other thing; the X820 is indeed the same thickness as a wafer biscuit. Just so you know.

A New Year

Welcome and a Happy New Year to everyone!

It's been a hectic few months here at gadget central. First of all there was the arrival of a baby daughter in September. Yes, mine/. Shock to the system, yes. No more time for gadgets? Err, a bit less but I do try. Completely new perspective on life? Mostly.

Ok, first newborn, then sold house in November, then sold second house bought and moved in to new house December. So, I think that pretty much sums up the reasons for not updating the blog in ages.

So, what's new? Well, I got a new mobile phone, a jelly bean dispenser and a robot. The phone was free, the bean machine a gift for Christmas, and the robot, er, a needless luxury purchase. Also upcoming will be a spot on the TomTom satnav unit I got for the summer hols last year. I just upgraded the TiVo's hardrive too, so maybe a bit on that. Plenty to be getting on with. And to I'll finish things off with one word to show I'm on the steady pulse of technological development: iPhone. Say no more!