Disable selected Excel Menu Commands

**antonf** · 10-23-2009, 02:47 AM

Is it possible to use VBA to disable selected Excel menu commands? Enabling the commands should be password-protected. It will have to be functional in both Excel 2003 and 2007.

Your effort and assistance will be appreciated!

**royUK** · 10-23-2009, 04:04 AM

I would strongly recommend that you do not do this. If anything goes wrong & the menus are not restored you will have annoy users.

Excel 2007 does not have menus, so the code would not work. You would need to hide the Ribbon

Much simpler to just use Full Screen

**antonf** · 10-23-2009, 11:53 AM

Maybe I should rephrase: I have often seen that certain menu/ribbon commands are grayed out. How do you go about doing this? (To me it as good as disabling a command, but I guess that is semantics)

**T.J. Lanza** · 10-23-2009, 03:18 PM

Originally Posted by antonf

Maybe I should rephrase: I have often seen that certain menu/ribbon commands are grayed out. How do you go about doing this? (To me it as good as disabling a command, but I guess that is semantics)

A large number of commands are greyed out when a worksheet is properly locked and protected.

**Jimmydageek** · 10-23-2009, 04:20 PM

Originally Posted by antonf

Maybe I should rephrase: I have often seen that certain menu/ribbon commands are grayed out. How do you go about doing this? (To me it as good as disabling a command, but I guess that is semantics)

You can disable many menu items pertaining to a sheet or workbook itself. If you just want to protect a sheet, in 2007, go down to the sheet tab and right click. Then select Protect Sheet. Then you check the boxes of the functions you want to allow users to have access to. You can also password protect each sheet. This comes in handy when you want different uses for each sheet.

Hope that helps.

**royUK** · 10-24-2009, 03:23 AM

Have you read my comments?

Have yo got the required skills to make good any problems created by your code messing up another user's settings?

As I said the code will not work for 2007 & earlier versions, they would need to be different.

If you remove these commands then your code also needs to restore them.

Why do you want to do this?

**antonf** · 10-26-2009, 08:43 AM

Originally Posted by royUK

Have you read my comments?

Have yo got the required skills to make good any problems created by your code messing up another user's settings?

yes, I have read the comments, but thought I didn't express myself very well. (English is my third language!) hence my follow-up post. NO, I definitely don't have the skills to repair the settings I mess up, not even on my own computer! Yes, I know all about fools who rush in where angels fear to tread!

As I said the code will not work for 2007 & earlier versions, they would need to be different.

If you remove these commands then your code also needs to restore them.

Why do you want to do this?

Before I get an infraction again, this is to some extent linked to another post I made

I have a macro that adds comments whenever a change is made in the sheet. I would prefer the comments to stay hidden. The sheets are fairly simple and will generally not require other comments to be added. This way I can keep track of who changed what and when it was done.

In short thus (for this specific case): I would like comments to be hidden, and a password is required to view/delete them.

I will rather address the other similar situations in separate posts.