Limiting custom menu selection based on sheet name

hello

i am considering the use of custom menus in an Excel application. is it possible to limit the selection of menu items for each worksheet? for example, sheet 1 all menu items are available, sheet 2 all but 2 menu items are available, sheet 3 all but 5 items are available.

thanks in advance

Not done it myself but it sounds perfectly possible.

You would probably want to use the Workbook SheetChange event and use a few if's or select case statement to decide which customised menu to use.

thanks chergh and royUk.

i was hoping to use the code created by Ron de Bruin http://www.rondebruin.nl/compatiblemenu.htm

if i introduce the following code from the Microsoft support pages into the loop, it should achieve my desired outcome? that is, if worksheet doesn't equal something, don't display...

Please Login or Register  to view this content.

i'm working with Excel 2003.

i'm using Example 1 as contained on Ron's page - using a worksheet that contains the menu elements used to create MyMenu in an Excel file.

yet my workbook has many sheets and i do not want each option to be available on every worksheet. continuing with Ron's example, i want "Wizard" to be available from "My Menu" on sheet1 and sheet2, but not available on sheet3 (if you download the Menu_97-2007 file you'll need to insert two more sheets)

i hope this helps you understand what i'm trying to achieve and how

thanks royUK - that's great!

you're adjusting the range depending on the sheet name to create differnt custom menus, where i was trying to have one custom menu yet make items active or inactive - yours is a lot simpler; amazing what a pair of fresh eyes and additional talent can do!

too true.

having said that, i've found part of the puzzle - the "enable" syntax and where it needs to be incorporated into the routine. now to figure out how to apply it to a range of sheet names, maybe using LIKE and the sheet name, or referring to a separate range of sheets?

http://www.erlandsendata.no/english/...nvbacbmenuxl97