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 that will list the controls on the active sheet (please try in new workbook). You can search column "D" for the ID = "855" that rorya mentioned.

Public Sub List_Controls()

Dim oCell As Range
Dim oCommandBars As CommandBars
Dim oCommandBar As CommandBar
Dim oControl As CommandBarControl
Dim oControls As CommandBarControls

Set oCell = Range("A2")

For Each oCommandBar In CommandBars
    oCell.Value = oCommandBar.Name
    oCell.Font.Bold = True
    Set oCell = oCell.Offset(1, 0)
    For Each oControl In oCommandBar.Controls
        oCell.Offset(0, 1).Value = oControl.TooltipText
        oCell.Offset(0, 2).Value = oControl.Caption
        oCell.Offset(0, 3).Value = oControl.ID
        
        Set oCell = oCell.Offset(1, 0)
    Next oControl
    Set oCell = oCell.Offset(1, 0)
Next oCommandBar

End Sub

Also to intercept keyboard shortcuts you might want to try "OnKey".

Application.OnKey "^1", "MyUserMessage" ' Ctrl + 1 calls MyUserMessage procedure

Gary

 

[OfficeUser]

How do I call up these menus you are referring to, I am unable to see the menu you reference. Thanks.

 

[Gary McMaster]

I'm not sure I know what you mean?

If you right click on a "row number" or "column letter" a different pop-up menu is displayed as rorya pointed out (not the "Cell" command bar addressed by my code). The format command on those different pop-up menus is not disabled by the code I originally posted.

rorya's code addresses all of them by "ID" number (855) no matter what commandbar contains them.

You could also address them individually if you need to be more selective:

Set oCommandBar = Application.CommandBars("Cell")'As shown in my original post

Set oCommandBar = Application.CommandBars("Row")

Set oCommandBar = Application.CommandBars("Column")

Gary

 

[OfficeUser]

I am not sure that is correct. Using your code, disables them all. I do see the difference when not using the code...

 

[RoryA]

I'm not sure what it is you are doing but Gary's original code definitely does not disable anything other than one Cell commandbar.

 

[OfficeUser]

Would this bit in the Workbook_Open Event be doing it?

.CommandBars("Worksheet Menu Bar").Controls("Format").Enabled = False

 

[RoryA]

Nope - that will disable one main control on the main menu bar. It will not disable all the format cells menu items.

 

[OfficeUser]

I am trying to figure out why its working correctly, seems odd though. Stay tuned...

 

[RoryA]

Are you sure you haven't protected the worksheet? Be a lot easier...(and just as secure)

 

[OfficeUser]

I only protected one column, all the other cells are editable/unlocked. To eliminate that issue I am trying it on one of my unprotected sheets within the workbook.