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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
