Disable "Format Cells" menu

[OfficeUser]

I have been able to disable "Format" from the main excel menu, but am stumped trying to disable it in the right click menu. Does anyone know the trick to doing this? Thanks.

 

[Gary McMaster]

Here's a sample. I hope it helps.

Gary

Public Sub Test()

Dim oCommandBar As CommandBar
Dim oControl As CommandBarControl

Set oCommandBar = Application.CommandBars("Cell")

For Each oControl In oCommandBar.Controls
    Debug.Print oControl.Caption
    If InStr(1, oControl.Caption, "Format") Then
        oControl.Enabled = False
        'oControl.Delete 'or delete control if desired
    End If
Next oControl

'Use the following statement to restore "Cells" menu to "as installed" condition
'Application.CommandBars("Cell").Reset

End Sub

 

[OfficeUser]

That works great. Thanks for the help.

 

[RoryA]

Don't forget Ctrl+1

 

[OfficeUser]

Actually, using the code he has posted, and also using code I had to disable "Format" in the main excel menu, that does not work. I will say that you taught me something new as I did not know about that shortcut. Thanks.

 

[Gary McMaster]

Thanks for the feedback. Glad it worked for you.

I would be inclined to let the user know why the "Format cells" option is disabled. Instead of disabling or removing the control you could change the "OnAction" property to display a user friendly message.

Maybe disable it after the user clicks it the first time and is given the notice. I find that it reduces the number of times I hear "my Excel menus are messed up".

oControl.OnAction = "MyUserMessage"

Public Sub MyUserMessage()

MsgBox "You are not authorized to format cells"
'Disable control here

End Sub

Gary

 

[RoryA]

You should also be aware that there are 2 Cell menus.

 

[OfficeUser]

That's a great suggestion, and I will likely use it. I have to share a spreadsheet with some folks that barely know the basics of excel. Not that it's a bad thing, but I am responsible for the overall effectiveness of the workbook.

 

[OfficeUser]

You should also be aware that there are 2 Cell menus.

I am unsure what you mean. Care to explain?

 

[RoryA]

Gary's code disables the Format... items on the Cell menu, but there are actually two of them so you'd need to loop and disable both. There are also Format Cells commands on the 2 Row and 2 Column shortcut menus. I would use:

Dim oControl As CommandBarControl

For Each oControl In Application.CommandBars.FindControls(ID:=855)
   oControl.Enabled = False
   'oControl.Delete 'or delete control if desired
Next oControl

which will disable all copies of the Format Cells command, no matter which toolbar they are on. (2003 and prior anyway)