Saturday, December 22, 2007

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.




55 comments:

Ashdonite said...

You are very clever, this is lovely and just what I needed. Many thanks!

Ninad said...

The macro is brilliant. Great work. But I can't use it without some tweaking. For us in the Middle East, off-days are Friday and Saturday. So the shadings need to be changed. Can you rectify this and add this as another option. That'll make it super

Steve said...

Thanks for the complements. It shouldn't be too difficult to offset the shading by a day to accommodate an alternate 'weekend'. Watch this space...

Ninad said...

I certainly wil..........but don't make it too long. 2009 is fast approaching and I need the planner soon. Great work. Look forward to more of these.

Steve said...

OK, done. Check the last option on the main dialogue. Hope that's what you were looking for!

Ninad said...

That's great. Hope I can tap your skills to make it more flexible. Here's what can be included further.

1. Shading or color option for weekends (either Fri-Sat or Sat-Sun) can be provided
2. If weekend is Fri-Sat, then "Different color for Sunday"

Steve said...

These are good suggestions - I have a couple more ideas that build on that as well as the result of a discussion with a colleague. I will try to make the changes soon - I'll post back here when I've uploaded the next revision.

Anonymous said...

I downloaded and got 'Runtime error 9' it wouldnt work had to to do a system repair on pc. What do you think?

Steve said...

Sorry to hear you have been having problems.

'Runtime error 9' occurs if the macro refers to a non-existent workbook or worksheet. There are two references to the workbook name within the macro - if you have renamed the file you will get this error. (If you have renamed the file, you can change the references within the code to match - look for the worksheet.activate statements.)

As for system corruption... the macro doesn't do anything file-wise (apart from refer to itself), so it's hard to imagine it affecting a system in such a way - although it's possible it's become corrupted or been tampered with - I'll check...

You don't give any details about what forced you to do a system repair ie. did yo have boot problems, problems with Office etc.

I'll check myself that everything still works then report back here.

Anonymous said...

Will it link and show entries made in outlook?

Anonymous said...

hey Steve!

I really like your work with this, it's exactly what I was looking for!

I wanted to let you know that I just created a 2009 year planner but the week naming function isn't working properly. As 2009 starts on a Thursday, it has started counting week 1 from the first Monday so all the weeks are out by 1. Just thought I'd let you know about this.

Thanks again for the fantastic job!
Kind regards

Steve said...

Thanks for the feedback guys. Let me start by saying that the import from Outlook thing has already been considered... It would be a nice feature but maybe goes outside the bounds of simplicity I'm aiming at with the yearplanner (read: it's too difficult lol). If it were implemented it would be a major upgrade.

Next is the week number thing. You know, I spent the whole of 2008 with a A3 laminated yearplanner with the week out by 1. I eventually fixed it for ver. 2 and it looks like it's still wrong! Kind of. Turns out there is more than one way of interpreting the first week of the year. Outlook provides three settings, so perhaps the yearplanner could have the option to alter the start week.

Lastly, to the poster who was having problems, I've checked the file hosted at box.net and it's fine so I'm not sure what your problem was. Sorry I can't be more help.

Steve said...

About the week numbers. My previous comment was partially correct. There was a fault in the week number code. It didn't exist... the macro treated the first week containing a Monday as week 1, which is wrong according to the diary, filofax and calendar I consulted today.

Like I said, I fixed this problem before, but somehow the new bit of code got deleted in the last revision. Put it down to poor version control for which I can only apologise.

The current version hosted at box.net calculates the week number correctly. Hope that helps.

diane said...

i have office 2003, can this be the reason why when i use your software i get a bug window after clicking the "start" window?
cant wait to use your planner

Ninad said...

Now this is what is call a macro. Great stuff man, I love it and thank you for incorporating my requests.

Have you gotten around to writing any more ?

Camilo said...

Simply Fantastic!! Extremly helpfull.
Many thanks!

Ninad said...

Hey Steve,

Happy New Year to you.

One suggestion.

For those who select Friday / Saturday as their weekend, instead of Monday shading, can you do Sunday shading, since this will be the first working day.

Regards,

Ninad.

PS.: This macro is getting better and better and bigger and bigger. Great work. Keep it going.

Glen.Collinson said...

Hi Steve. Happy new year. Just wanted to say, having been looking for a yearplanner tool on google, being a big fan (and not bad at it) of excel, and loving gadgets, i love the blog, and this tool ROCKS! great work, and many thanks for making it free - you could probably sell it!
Glen
http://www.GlenCollinson.com
http://LivingTheLifeYouWant.wordpress.com

dn said...
This comment has been removed by the author.
iere said...

What a great tool! Thank you Steve! MS should buy it from you!

Re the 'Runtime Error 9' problem bought up by Anonymous:

I got that when I copied and pasted data into 'Events' from another spreadsheet, even when pasting as text. To work around it I pasted the Excel data into a Word Doc, then copied that, and pasted it as Text.

Re: importing data from another source - perhaps this VB code that creates a similar planner from an Outlook calendar might provide some inspiration?

http://niveauverleih.blogspot.com/2008/12/outlook-based-multi-month-calendar-v2.html

paul said...

Excellent! Just i needed.
It's gonna take pride & place on the front of my fridge :)
For Outlook, simply export calander to a CSV & cut/paste in your dates, couldn't be any simpler.
I too got the runtime error, this due to me having other spreadsheets open.
Thanks
P.S. Where can i send a small donation of my appreciation :)

Jen Taylor Friedman said...

This is awesome. Just absolutely what I needed. Thank you so much for taking the time to make it and put it online.

Stefan said...

Hi, You are a genius. Thank you for the nice site and info!

Steven said...

Hello everyone. I've just updated the yearplanner for the first time in ages. It's a bugfix and tidyup - hopefully making even easier to produce clean, simple planner charts.

Thank you for your kind words thus far - hope you enjoy the revised edition!

Jeffa said...

Hi Steve, I have started to use your Yearly Planner macro, which is great, but once I add another Event to the planner, I cannot refresh the macro without resetting the whole calender. The control panel is not visable to select Show Events.
Cheers Jeffa

Steven said...

If you close the Control Panel, you can easily get it back by clicking the 'Start' tab (at the bottom), then clicking the big Start button. Hit the Preview button again and your additional events should show up (you may need to close the Control Panel again to manipulate the sheet view to check).

Nicky said...

Nicky
This is great.

Do you think you could make a Google Doc version of it ?

I think it would be great in their templates.

They have nothing in their Chrome apps that has a simple planner.

cheers

Nikcy

Steven said...

Hi Nicky
Thanks for the support.
I'll be honest - I haven't looked into using Google Docs for this until now.

Because Google Docs doesn't support Visual Basic Script as utilised by my yearplanner, pretty much all the functionality would be lost.

However, like the PDF samples linked to at the top of the page, I could create a set of 2012 (and future) planners, import them into GD, then make them public.

I guess this would make the planners platform independent to a degree, even if it means losing some of the customisation possible with the Excel version.

This is something I will be definately looking into.

Steven said...

While I'm in comment mode, just a quick note to say there's a major revision to the Yearplanner maker in the, er, making.

For those that need it, the new version will allow the 12 months to be split over the year, say starting July, finishing June the following year. Ideal for tracking the school year for example.

I'm hoping to have it ready before the end of this year (2011) so watch this space.

Anonymous said...

Great work, just what i've been looking for for ages now.
Thanks for going to the trouble in producing this.

DeepThought said...

Steven,

Great. just what I was looking for.
I made a couple of changes: Allow colour for "Weekends" in the "Different Colour For" combo box. Also added a third column to the events sheet for "type" and added a list box to the selection screen so that you can select which types of events you want to print. Very happy to send this back to you if you want, but not sure how.

Steven said...

DeepThought - As I'm always looking for ideas for improving the functionality of the planner maker I'd appreciate the input. My email is the same as the twitter name in the sidebar - just add googlemail.com ;) I have a new version on the brink of publication - I just need time to rev up the blog post and upload the file. If you send me the .xls, I'll try and incorporate your mods before I publish. Thanks for your interest.

Anonymous said...

Does it work on Macs? if so are there different instructions? Its such a brilliant
idea.

Steven said...

Thank you. I just tried it on Office:Mac 2011 for the first time... surprisingly, it works!

Sure the dialog is not as neat - the little colour swatch buttons don't show colour, and the macro runs very very slowly, but kudos to Microsoft for making it possible at all.

You'll need to click the 'Enable Macros' upon opening the document for the first time. Apart from that everything is the same.

Peter said...

I can't get it to work? START gives a run-time-error '-2147483640(80000008)'

I've opened it on mac with Office 2011and macros enabled - can anyone help?

Jon thinks said...

BRILLIANT, been after something like this for about 7 years. BTW good choice of car and gadgets :-)

Steven said...

Hi Peter,
Thanks for trying out my planner maker - sorry you're having problems... perhaps you can try the following:

On your Mac go to System Preferences>Language & Text>Region

Set region to UK or US.

Try the macro again.

This was from the following link:

http://answers.microsoft.com/en-us/mac/forum/macoffice2011-macexcel/now-does-not-work-in-excel-2011/5dec00b4-3552-45ec-9fcf-a222f4930f7e?msgId=93bb51df-e902-4e8d-bfb3-62c59087d170

Hope this helps!

Steven said...

Jon,

Thanks - yes there seem to be plenty templates for planners out there... but few that allow quick and easy customisation. The car... the car has been virtually trouble free so far (touch wood) and has plenty of gadgets itself to keep me happy. Might be good do do a spot on on that soon ;-)

Anonymous said...

Fantastic work, I thank you for sharing it with us. If I wanted to set recurring shifts (days & nights) to use this for a shift rota planner where do you recommend I start.

Steven said...

I'm happy you're finding the planner useful.

To do rotas, use the Events sheet and Excel's Autofill feature to set out a block of dates for each employee.
Put the employee name in column B and fill down. Colour each date block as you se fit. Run the macro to see the effect.

For help on Autofill, consult google... ;)

Anonymous said...

The vertical is just what my band needed, We access it on line to fill in gigs and holidays.
Thanks

Anonymous said...

Nice work Steven.
Thanks for your generosity in sharing you creation

Anonymous said...

Tq form Malaysia

ibx1 said...

For four years I've had to fiddle with my Excel template every January to change my planner ...... No more will this be necessary thanks to you Sir .... you are truly a GOD .... I worship thee ......

RUBEN FRANCO said...

Awesome!!! you did such a nice Job!!! Excellent! Thank you so much!!

RUBEN FRANCO said...

Awesome!!! you did such a nice Job!!! Excellent! Thank you so much!!

Anonymous said...

updated thanks

can't thank you enough !

TheTassieBFG said...

Hi just stumbled on this and it looks great.. I am wondering if it is possible to bold certain text in the events and have it appear on the calendar. I thought i could just use search and replace after the spreadsheet had populated but turns out that converts the entire cell contents and not just the text in question. The purpose for this is to differentiate multiple events on the same day, ie public holiday, school holiday,

Jeff Weaver said...

Hi Steven

I like your product but is it possible to create a Perpetual Planner using your template? I need to have planners which run on continually, from one year into the next, rather tha, to have separate planners for each year.

Thanks very much.

Jeff
www.weaverartistmanagement.com.au

Steven said...

Hey Jeff,
The planner has the ability to split the year, so if you need to start on June for example, you can do that, showing June in the 1st row or column, rolling around to May of the following year. The restriction is that only 12 consecutive months are shown at a time. Hope this helps!

Rachel Rosen said...

Hi Steven

got this tool working quite nicely with a csv export from outlook.
...however, 2 limitations I have found.
1. every time I add an event I have to turn the highlights on and off to refresh the planner. a 'refresh' or 'update planer' button on the events tab would be cool.
2. less trivially, I have not found a way of having more than one thing on the same day - however this is exactly why I am looking for a tool like this, to see how events etc are stacking up, also combining against public holiday etc.
may be there is a way of doing this?

good work anyway!
thanks
Rachel

Terry Dou said...

Heya Steven
Have you had any issues with your macro working in Excel 2016? Call me ignorant but the poor thing doesnt seem to function with 2016. Seems to crash. I've turned off any macro security excel has, but still no dice.

Steven said...

Hi Terry,
Thanks for the update! Unfortunately I'm not in a position to easily test the macro against newer versions of Excel as my workplace is still on Office 2010 and the Mac version I have is stuck at 2011!

TBH I have not looked at the code for some time however I might revisit it to tidy up the window management (allowing the app to exist alongside other open workbooks) - it may fix whatever issue you are having.

Terry Dou said...

Thats ok Steven
I do have a license for Office 2010 so ill install that for the short term so i can use your AMAZING spreadsheet. I have searched all over the internet to find exactly what you have made, so if its not working with 2016 ill just do what i have to do be able to use it, which means chucking 2010 on my computer.

You are an Excel-God among men

Steven said...

Ha ha steady on, I wouldn't go that far! But I like to get more leverage out of Office by scripting - there are a lot of processes people use that can be automated by the use of a little (or big) script. I even have some Powerpoint code to programatically draw things.

FWIW I still use the Planner for logging shifts, spotting vacation clashes etc. - I never got used to electronic calendars.