Macro based yearly calendar template

I have added to the downloads page a yearly calendar template. This is a dynamic template that will allow you to generate a calendar for any year between 2001 and 2016. There are two combo boxes for the user to select the year and the start date of the week. Each selection will trigger a macro that will generate the calendar. Here are a few notes to consider:

To Enable Macros:

Select : Tools —> Macro —> Security —> Security Level —> Medium

Names of days and months

I have put the names of days and months as tables in the “Settings” worksheet. This will allow you to translate them in your own language if necessary. You have to unprotect the sheet first before you can do that.

Protection

1. All sheets are protected to avoid accidental change of data and formulas. If you want to make some changes like formatting for example, then you can unprotect the sheet. Make sure however that you don’t mess up the formulas.
2. Columns X and Y are hidden but not protected. These columns receive data from the two combo boxes for the year and the start date.

Printing

You can print the calendar just like any other Excel worksheet. The print area is set in a way that it will only print the year and the 12 boxes for the months, i.e. without the combo boxes.

VBA code

If you want to see the code behind all this, then switch to visual basic editor. The main routines are in module2, but some of the code is within different events of the workbook and the combo boxes.

Your feedback is appreciated.

Applies to: Excel 2003

Macro based monthly calendar template

I have added to the downloads page a dynamic monthly calendar template. There are three combo boxes for the user to select the month, year and the start date of the week. Each selection will trigger a macro that will generate the calendar. Here are a few notes to consider:

To Enable Macros:

Select : Tools —> Macro —> Security —> Security Level —> Medium

Proverbs

1. This calendar uses function RANDBETWEEN from the analysis toolpack to generate a random number for the proverb lookup.
2. Every time recalculation occurs you will get a different proverb (hit PF 9 to recalculate)
3. If you don’t have the analysis toolpack then copy the following formula in cell “A12” and remove the quotes. You will get a proverb depending on the selected month.
“=VLOOKUP(I2,Settings!$C$2:Settings!$D$13,2,FALSE)”
4. The proverbs are stored in the “Settings” worksheet. If you don’t like my selection then you can replace them with your own.

Names of days and months

I have put the names of days and months as tables in the “Settings” worksheet. This will allow you to translate them in your own language if necessary. You have to unprotect the sheet first before you can do that.

Protection

1. All sheets are protected to avoid accidental change of data and formulas. If you want to make some changes like formatting for example, then you can unprotect the sheet. Make sure however that you don’t mess up the formulas.
2. Columns H and I are hidden but not protected. These columns receive data from the three combo boxes for the month, year and start date.

VBA code

If you want to see the code behind all this, then switch to visual basic editor. The main routines are in module2, but some of the code is within different events of the workbook and the combo boxes.

Your feedback is appreciated.

Applies to: Excel 2003