How to prevent deleting rows or columns in a shared unprotected workbook?

When you share a workbook to others, how can you prevent others from deleting the columns or rows of your shared workbook without protecting the workbook? Here I find a VBA code that can prevent others from deleting the columns and rows in a shared workbook.

Prevent deleting rows or columns in a shared workbook with VBA

Prevent deleting rows or columns in a shared workbook with VBA

1. Press Alt + F11 to open a Microsoft Visual Basic for Application window.

2. Click Insert > Module to open a Module window, then copy the following VBA to the window.

VBA: Prevent deleting rows or columns.

Sub StopDeleteRowCols()
'Updateby20221221
Dim xBarControl As CommandBarControl
Application.OnKey "^{-}", ""
For Each xBarControl In Application.CommandBars.FindControls(ID:=293)
    xBarControl.Enabled = False
Next
For Each xBarControl In Application.CommandBars.FindControls(ID:=294)
    xBarControl.Enabled = False
Next
End Sub

3. Click Run button or F5 key on the keyboard to run this code.

Then when others right click to delete selected columns or rows, the Delete option cannot be applied in the context menu. See screenshot:

Also, it prevent users to use shortcut Ctrl + - keys to delete rows or columns.

Note:

(1) If you have shared your workbook with Review > Share Workbook, you can’t add any VBA macros any longer. Therefore, you’d better add the VBA before sharing the workbook.

(2) This VBA will disable the Delete in the context menu in all workbooks when you right click selected columns or rows.

(3) To restore the Delete in the context menu, please replace both False with True in the VBA above, and run the VBA again.

Unlock Excel Magic with Kutools AI

• Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
• Custom Formulas: Generate tailored formulas to streamline your workflows.
• VBA Coding: Write and implement VBA code effortlessly.
• Formula Interpretation: Understand complex formulas with ease.
• Text Translation: Break language barriers within your spreadsheets.

Enhance your Excel capabilities with AI-powered tools. Download Now and experience efficiency like never before!