How to Disable Excel Comments from Right-Click Menu Using VBA
Question details
The user wants to prevent individuals from modifying workbook comments by removing or disabling the Insert, Edit, Delete, and Show/Hide Comment options from the right-click context menu.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Restricting users from managing cell comments via the right-click shortcut menu to maintain standardized workbook formatting and prevent unauthorized annotations.
- Observed behavior
- By default, users can freely right-click any cell in Excel to interact with comments. The goal is to make these specific commands inactive or hidden in the context menu.
Ensure that the Developer tab is enabled in your application's ribbon to access the VBA editor, and remember to save your file as a Macro-Enabled Workbook (.xlsm) so the code remains active.
Use a VBA Macro to Disable Right-Click Comment Commands
Run a simple VBA script targeting the "Cell" command bar to disable all comment-related menu controls.
This approach uses the Application.CommandBars property to directly target the default right-click context menu for standard cells. By setting the Enabled property of comment-specific controls to False, users will no longer be able to click them.
Launch your workbook and press the Alt + F11 keys simultaneously to open the Visual Basic for Applications (VBA) window.
In the VBA editor, navigate to the top menu and click Insert, then select Module to create a blank coding space.
Copy and paste the following code into the new module window: Sub Disable_Comments() 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
Click inside the code and press F5, or select Run > Run Sub/UserForm from the top menu. Return to your worksheet and right-click any cell to verify the comment options are disabled.
Manage VBA Macros seamlessly in WPS Office
WPS Spreadsheet provides excellent support for VBA macros, allowing you to easily customize command bars, automate repetitive tasks, and fully protect your workbooks without performance lags.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and complete the installation process.
- 2. Open the Macro-Enabled File: Launch WPS Spreadsheet and open your existing .xlsm or .xlsb workbook.
- 3. Run VBA Scripts via the Developer Tab: Navigate to the Developer tab on the ribbon, open the VBA Editor, and run or manage your macros seamlessly.
Frequently Asked Questions
How do I re-enable the right-click comment options later?
You can restore the right-click comment functions by running a similar VBA macro, but changing '.Enabled = False' to '.Enabled = True' for each of the respective controls.
Does this macro prevent comments from being added via the Review tab?
No, this specific macro only targets the cell right-click (context) menu. To completely prevent comment modifications across the entire application, you must apply 'Protect Sheet' and ensure the 'Edit objects' option remains unchecked.
Why did the macro stop working when I reopened the file?
This happens if you saved the file as a standard Excel Workbook (.xlsx) which strips out VBA code, or if your macro security settings disable macros by default upon opening. Always save as a Macro-Enabled Workbook (.xlsm) and click 'Enable Content' when prompted.
