Those who’ve worked with elaborate multi-worksheet dashboards would realize the usefulness of adding a customized menu option. Apart from other things, a customized menu option can be used to guide the user to specific portions of an excel spreadsheet. For example, if you have a long report consisting of multiple charts, pivots, images and data fields, you may want to spare the reader the effort of locating the most useful (or important) portions by creating a new menu option and linking all those important areas a drop-down values.
For example, if you have an important project report prepared in excel, you can simply link up the important areas such as data gathering, data analysis and recommendations as drop down values to a new menu item called ‘Table of Content’ – and save the time the reader may have to spent scrolling and searching for that section. If the report is something that will be used very frequently, this can save a great deal of effort and monotony. This can also be useful in scenarios where the user has to carry out some bit of simulation – you can simply link the most important scenarios up as options in the drop down, say, summary of the test results after removing the outliers or looking at a specific ‘cut’ of data pertaining for a specific segment.
OzGrid has many useful sections on adding and modifying custom menus. I picked up one of the examples they had and then modified it so that each worksheet in my workbook can now have their its own custom menu. This involves coding in vba. A word of caution – please be a little careful when working with the code – you may end up modifying the existing menu bar (which can be restored back with a little effort).
The idea here is create a menu option the selected worksheet (or as needed) and when one moves from one worksheet to another, delete the menu option for the sheet being deactivated and the recreate the one getting activated.
Important here is to have the menu creation code executed first when the procedure Workbook_Open is called so that the menu are displayed for the first sheet getting activated. Also close the door before you leave by deleting all menues in Workbook_BeforeClose procedure. You can use either the worksheet.activate function or the Workbook_SheetActivate procedures to call for the construction of menus when a particular sheet gets activated.