Disable Insert Comment on Command bar

[Bering]

Hello,

I would like to disable Insert Comment when right clicking on any cell of a spreadsheet.

Using the code below I have managed to disable "Delete Comment, Edit Comment, Show/Hide Comment", however this does not seem to work for Insert Comment. Any ideas?

Thank you.

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
On Error Resume Next


         With Application.CommandBars("Cell")

            .Controls("Insert Comment").Enabled = False
            .Controls("Delete Comment").Enabled = False
            .Controls("Edit Comment").Enabled = False
            .Controls("Show/Hide Comments").Enabled = False


        End With

End Sub

 

[DanteAmor]

Try:

Application.CommandBars("Cell").FindControl(ID:=2031).Enabled = False

 

[Bering]

Thanks Dante, I tried that but it did not work ?

I will use the code below as a workaround:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Cancel = True
MsgBox "Right click disabled on this sheet!"
End Sub

However, I have now realised that users could still insert/delete/edit comments from the review menu of the ribbon

I tried this in Thisworkbook but nada.... If that can help I am using Microsoft Office Professional Plus 2016...

Private Sub Workbook_Activate()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
    Application.DisplayFormulaBar = False
    Application.DisplayStatusBar = Not Application.DisplayStatusBar
    ActiveWindow.DisplayWorkbookTabs = False

End Sub

Private Sub Workbook_Deactivate()

    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    ActiveWindow.DisplayWorkbookTabs = True

End Sub

Thank you

 

[Yongle]

@DanteAmor
- is this an Excel version issue perhaps ?
- did you test on Excel 2007
View attachment 2040

 

[DanteAmor]

Thanks Dante, I tried that but it did not work ?

I will use the code below as a workaround:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click disabled on this sheet!"
End Sub

Thank you

Check the following:

Customizing Context Menus in All Versions of Microsoft Excel

docs.microsoft.com

@DanteAmor
- is this an Excel version issue perhaps ?
- did you test on Excel 2007
View attachment 2040

It works for me in the 2007 version

 

[Bering]

Hello, I have removed some conditional formatting from my code and now everything works as expected...
Thanks for your help.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback. ?