Provides Professional Knowledge about Password/Data Recovery and Product Key

2 Options to Prevent Excel Sheets from Deletion

Is it possible to protect all worksheets or a particular sheet from deletion? If it is, how can you do to restrict any users from deleting excel sheets? Here 2 options in this passage are available for you if you need, which will disable delete sheet option in Excel.

Option 1: Prevent Excel Sheets Deletion by Workbook Structure Protection

As is known, once workbook structure is protected with password, unless you could get the right password or remove password to unprotect excel workbook structure, otherwise, you will not insert, delete, rename, remove, copy, hide or unhide worksheets in this Excel workbook.

So why not take this Excel feature to protect Excel sheets from being deleted?

1. Open your Excel workbook that includes worksheets you need to protect.

2. Tap on Review menu and click Protect Workbook in Changes group.

choose to protect workbook structure with password

3. Confirm Structure is checked and enter a password in following dialog Protect Structure and Windows.

enter password to protect workbook structure

4. Click OK and reenter the same password.

reeneter password to protect workbook structure

5. Click OK then you can see all the worksheets become undeleted.

cannot delete excel sheet without password

Tips: Only after unprotecting excel workbook, can you do anything again on this workbook including excel sheet deletion. If forgot password to unprotect excel workbook structure, what you need is only one click on powerful software Workbook Unprotect Genius to remove forgotten password.

unprotect workbook to unprotect sheet from deletion

 

Option 2: Prevent A Particular Sheet from Deletion with VBA

If you just want to prevent users from deleting one particular sheet, try this option, because option 1 will not be available. But if you would like to protect all sheets from deletion, this method also works.

1. Open Excel workbook, and right click on any sheet tab. Then select View Code.

right click sheet to open vba editor

2. Visual Basic Editor is opened. (Also you can press Alt + F11 to directly open it.)

successfully open VBA editor with shortcut keyboard

3. Double-click the sheet on the left pane you want to protect, and paste following codes in the right pane.

Private Sub Worksheet_Deactivate()
ThisWorkbook.Protect , True
Application.OnTime Now, “UnprotectBook”
End Sub

enter VBA code to protect excel sheet

4. Right-click the VBA Objects then on the left and select Insert > Module to create a module.

add a module for excel VBA project

5. Enter following codes in the right white area of Module.

Sub UnprotectBook()
ThisWorkbook.Unprotect
End Sub

add codes to module to deactivate excel workbook

6. Then click Save button in VBA editor and click No when you get a pop-up warning message “The following features cannot be saved in macro-free workbook“.

confirm to save macro to excel workbook

And in following Save As dialog box, choose to save it as Excel-Macro Enabled Workbook (*.xlsm) and click Save button.

save as excel macro-enabled workbook

7. Now open the .xlsm file in Excel and try to delete sheet in the macro-enabled workbook.

try to delete excel sheet in macro-enabled workbook

When you see a message, click “Delete” and you would find it is prevented from being deleted, because workbook has been protected and cannot be changed anymore.

confirm to delete excel sheet

prevent deleting sheet in xlsm workbook

Related Articles:

Comments are currently closed.