Customizing Excel Worksheet Menu Bar without VBA coding

[Sylvia]

Dear All,

I'm puzzled by an Excel Workbook file which was handed over to me by an
ex-colleague.

The workbook has a customized worksheet menu bar.
When you open the workbook, it will hide the Edit, View, Insert,
Format, Tools, Data, Window, Help menu and the Save and Save As
sub-menu in the File Menu.

When you close the workbook, the worksheet menu bar will be reset and
all the menus are available again.

I know that this can be done using macro, but I couldn't find any
macros attached to this file.

When I open the file and disable the macro, the worksheet menu bar
still gets customized.

I tried to look for custom menu bar in the workbook, but couldn't find
any as well.

Does anybody has any idea how the above mentioned can be done?

I would be glad to share the workbook as long as I can get an answer.

Thank you.

Regards,
Sylvia

 

[Dave Peterson]

Maybe you didn't look in all the locations that macro could hide??

Did you look under the ThisWorkbook module?

Debra Dalgleish has some instructions to help clean up remnants of macros:
http://www.contextures.com/xlfaqMac.html#NoMacros

Maybe you can use it to help find your code.

 

[Sylvia]

Hi Dave,

I have removed all inserted modules, checked that ThisWorkbook and
Sheet1 has no macro.

Like I've mentioned earlier, even when I disable macro, the menu bar
get customized.

Could it be that the macro was written in older version of Excel and
when I open in a new version it gets incorporated?? When I first open
the file, it shows the informational message about shifting the macro
into the VBA Editor.

Regards,
Sylvia

 

[Dave Peterson]

It sounds like the macro originated in xl95 (when there were macro sheets).

xl97 introduced the VBE (where your macro moved). That movement shouldn't make
a difference.

Any chance that there is some other old XLM macro code hanging around?

 

[Sylvia]

Hi Dave,

How do I check for the old XLM macro code?

Just notice another behaviour of the workbook (let's call it A). After
I close the workbook A, create a new workbook and do some editing on
the new workbook, it will prompt me if I want to enable the macro on
the workbook A and then it will launch the workbook A.

This behaviour will go away if I exit the whole excel application after
opening workbook A before doing editing other workbooks.

Regards,
Sylvia

 

[Dave Peterson]

Try Format|Sheet|unhide
to see if there are any Macro sheets (they could be hidden if you don't see
them)

And if that workbook opens right after you click an icon, then maybe you have
that icon assigned to that workbook. Excel is smart enough to know when it has
to open a workbook.

Another cause could be some sort of OnTime macro. The developer could have said
wait x minutes/seconds and then reopen the workbook. (This one may be
farfetched, though.) But you can still look for that.

 

[Dave Peterson]

Jim Rech has a utility that you may want to try:
http://www.oaltd.co.uk/MVP/Default.htm
look for RemoveMenus.zip

 

[Sylvia]

Hi BizMark,

Thanks for the information. I think you are right. The workbook was
first created in 1996.

Hi Dave,
Thanks for the information on the utility. I've finally managed to get
rid of the phantom customized menu.
Now will have to write VBA codes to manipulate the menu bar.

Regards,
Sylvia