2 Ways to Protect Excel Sheets from Being Deleted

Protect Excel Workbook

If you share your confidential Excel workbook with others, you may don’t want the worksheets to be deleted by someone else. So you need to take steps to protect the Excel worksheets from being deleted. Now, this post will show you two ways you can protect Excel sheets from being deleted.

Use workbook protection to protect Excel sheets

If you want to prevent all worksheets in an Excel file from being deleted, you can simply use the Protect Workbook feature under the Review tab of Excel. This feature can protect the structure and windows of an Excel workbook from being changed. Once this feature is enabled, the users of this Excel workbook won’t be able to add, delete, rename, or moving the sheets. Here are the steps.

Step 1: Open the Excel file.

Step 2: Click the Review tab, and then click the Protect Workbook command under the Review tab.

Click Protect Workbook

Step 3: In the Protect Structure and Windows dialog, check the Structure and Windows option, type in a password if you want, and then click OK. Note: The Windows option is greyed out in Excel 2013/2016.

Protect structure

Tips: If you assign a password for the workbook protection, keep the password in a safe place. If the password is forgotten or lost, it cannot be recovered and you have to remove the password using a third-party tool like Workbook Protection Refixer.

Step 4: Save the Excel file for the changes to take effect. Now all the sheets in this Excel workbook can’t be deleted.

Tips: If you want to allow the deletion of the worksheets, just need to remove the workbook protection using the Unprotect Workbook command.

Use VBA editor to protect Excel sheet from being deleted

If you just want to protect one or some particular sheets from being deleted, use the VBA editor to do that. Steps are as follows.

Step 1: Open the Excel file.

Step 2: Select the sheet you want to protect from being deleted, by clicking on the tab at the bottom of this Excel file. Right click on the tab and select View Code.

Select View Code

Step 3: Type in the following codes in the Code window. Replace yourpassword with your real password.

Private Sub Worksheet_Activate()
ThisWorkbook.Protect “yourpassword”
End Sub

Private Sub Worksheet_Deactivate()
ThisWorkbook.Unprotect “yourpassword”
End Sub

Type in codes

Step 4: Click the Save button or press Ctrl + S to save the VB project. When prompted with the following dialog, click No.

Click No

Step 5: In the Save As dialog, choose Excel Macro-Enabled Workbook from the Save as type drop-down list, and then click Save.

Choose excel macro enabled workbook

Step 6: When prompted with the Privacy warning, click OK.

Click OK

Step 7: Now close all the windows.

Step 8: Open the .xlsm file in Excel. You will see a Security Warning at the top that says Macros have been disabled, click the Enable Content button to enable Macros.

Click Enable Content

Step 9: Shift to another worksheet and go back to this worksheet again to activate the VBA codes. From now on, when you right-click on this sheet tab, you will find the Delete command is disabled and greyed out. By this way, you protect this sheet from being deleted, while other sheets still can be deleted.

Delete is disabled

You May Also Like

About the Author: Darren