Excel Monthly Family Meal Planner

Hello,

the following steps work for me: first add the new recipe in the recipes sheet. Make sure that you enter it in the table, i.e. don't leave any blank rows between existing and new recipes.

Next add the ingredients. The new recipe will show in the recipe dropdown. 

Now you can use the dish in the meal plan.

I found that the template does not work with regional settings where the date format is different to MDY. My defaults are DMY and I could not get the shopping list button to work at all.

The reason is that a hidden sheet holds the date filters used in VBA. But VBA is so very fixed on the US date order MDY that filter won' work for regional settings that use DMY.

To make the template work for a DMY date order, change the following line of VBA from

[WeekPlanCriteria].Offset(1).Resize(1) = Array(">=" & [ShoppingStartDate], "<=" & [ShoppingStartDate] + 7)

to  

[WeekPlanCriteria].Offset(1).Resize(1) = Array(">=" & Format([ShoppingStartDate], "MM/dd/yyyy"), "<=" & Format([ShoppingStartDate] + 7, "mm/dd/yyyy"))

After that, the template will work in any regional setting.

Awesome thank you!

We are planning to try it out both ways (7 days and 28 days) as we get paid monthly and this helps us out a lot.

Now... is there a way to and a new column to the ingredients  worksheet to have "food type" as the header so we can arrange the sort to list like types of food (ie. canned, protein or produce)?  If I were to and the "food type" column would this mess up the current worksheet?

Thank you so much for your assistance!!

Paul

Hello,

the number of days for the shopping list is set in the VBA macro behind the scenes. You can change the number of days, but it will be really, really difficult to change the shopping list to monthly, as in from the first of the month to the last day of the month.

If you want, you can change the setting to four weeks instead of one.

First, let's correct the error of the template returning 8 days worth of shopping instead of 7 for the default of 1 week:

While you have the file open, hold down the Alt key and hit the F11 key. This will open the VBA editor.

In the left hand navigation panel of the VBA Editor expand the + sign next to the word "Modules" and then double-click the Module1. Now you will see the VBA code in the big code window. You should see something like this:

The 10th line of text is this:

[WeekPlanCriteria].Offset(1).Resize(1) = Array(">=" & [ShoppingStartDate], "<=" & [ShoppingStartDate] + 7)

This sets the search criteria for the Shopping period from the Start date to a date that is less than or equal to the Start date plus seven days. Which will result in eight days being returned.  Remove the = sign in the combination "<=", so the line reads

[WeekPlanCriteria].Offset(1).Resize(1) = Array(">=" & [ShoppingStartDate], "<"& [ShoppingStartDate] + 7)

and you will get seven days of Shopping in the list. Along the same lines, you can change seven days to 28 days, for example, by using

[WeekPlanCriteria].Offset(1).Resize(1) = Array(">=" & [ShoppingStartDate], "<" & [ShoppingStartDate] + 28)

That will return four weeks worth of shopping list.

Let me know how you get on.

It is this one

http://office.microsoft.com/en-us/templates/monthly-family-meal-planner-TC103987059.aspx

I believe it is in their macros or button settings...I have not used Excel for a while and I just cant remember where to look.

Paul

Can you share the planner you are looking at, is that a template from ms?

If so, i don't think there is much to customize.