Drop down cell not showing all options/Significant loss in functionality

**mdunc** · 09-03-2012, 06:17 PM

Hello. The first problem with my issue is that I don't know the proper terminology to use to describe what is going wrong. I am a sports nutritionist and started only working online in Jan 2012. I use a spreadsheet created by an unknown source. The purpose of the spreadsheet is to have a "library database" of food choices including each food's macronutrient content and total calories and after launching a menu creator I can create custom menus from the foods in the library. I figured out how to add foods to the template and how to launch the menu creator, but recently as my library has grown, after I launch a menu the drop down box in column A doesn't show all of the food options entered in the template column P. When I save the document is warns me that there is a problem "significant loss in functionality", but I don't know how to fix it.

I have attached my master copy of the document with 1 menu launched labeled "high". Any help would be GREATLY appreciated!!

**Richard Buttrey** · 09-03-2012, 06:20 PM

Hello and welcome to the forum,

Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

As this is your first post I'll change it for you, but please note for future occasions.

**mdunc** · 09-03-2012, 06:35 PM

ok, thank you Richard. I appologize. I'll be more detailed next time.

**Richard Buttrey** · 09-03-2012, 07:03 PM

Hi,

See attached.

The problem was that the originator of the macro had 'hard coded' the range P1:V150 on your template. That's never a sensible thing to do as you've discovered since once you add data on rows below 150 the macro won't use them.

I've substituted a dynamic range name sp now it won't matter how many rows of food data you add.

I suspect your significant loss of functionality is caused when you try to save an Excel 2007/2010 .xlsm file as the older Excel 97/2003 .xls format.

The attached is a .xlsm file which should not display the message if you use it with Excel 2007 or 2010. If it does post back.

**mdunc** · 09-03-2012, 08:48 PM

Richard,

THANK YOU!!!!!!! Yes, that does make sense. I use Excel 2010 myself, but was saving it as 1997-2003 so any of my clients that had older versions could access it. Should I always save it as ".xlsm"? If you don't mind me asking...how much, if any, can it edit in the template without it messing up the formulas? Can I add rows to the template after the existing rows A 1-9? I am thinking in terms of needing more ingredients if I were to create a recipe? Thank you for your help!

**Richard Buttrey** · 09-04-2012, 12:56 PM

Hi,

I've changed the code so that you can now add rows to the A1:A9 section. To be strictly correct you shouldn't just add rows since that will also introduce a gap in your range of ingredients, and gaps in tables like this aren't usually a good idea. You should select A2:In (where the difference between n & 2 is the number of additional A:I cell 'rows' that you want to add) and choose Insert Cells and the option to move cells down.

Since the code has now changed I urge you to save a copy of your original in case things move out of line. I'm not sure why you need to copy the ingredients to all of the sheets you add, normally it's sufficient to have just one table. The macros also could be much simplified, but other than the simple changes I've made to accommodate these requests I've left things as they are.

**mdunc** · 09-04-2012, 03:40 PM

Thank you.
Like I mentioned previously, I didn't create the document. I actually found it from another forum a few years back when someone posted their diet. When I found it I thought I found a gold mine bc of the capabilities in creating meals plans so quickly....I hadn't previously used excel except to organize a meal, never calculate anything. I am very interested in simplifying the macro and even making the spread sheet more efficient.

**mdunc** · 09-04-2012, 04:02 PM

where so I go or click to view the macros? What is the table called? Is there a simple way to tell me how this table was made? I've tried researching it, but I don't know what terms to "google" in order to watch the correct videos etc.

**Richard Buttrey** · 09-04-2012, 04:21 PM

Hi,

The macros can be seen by either using Alt-F11 or choosing macros from the Developer tab.

If you're starting with macros from no prior knowledge, just watching a video may not be the best route. Read postings in this forum to try to get a flavour of the way things are done, or get a book about macros or VBA as it's often called. There are several on Amazon.

Regards