How to Protect Excel Sheet from Deleting (3 Simple Methods)

Method 1 – Use Password to Protect Excel Sheet from Deleting

Steps:

From the “Home” ribbon click on “Protect Sheet”.

A new window will appear asking for a password to protect.
In the “Password to unprotect sheet” provide a password of your choice and press OK to continue.

Another window will open asking for the confirmation of the password.
Enter the same password you typed in the previous window.
Click OK.

This way you can protect your excel sheet from deleting with a password.

Method 2 – Activate Read Only Mode to Protect Excel Sheet from Deleting

Steps:

Click the “File” option from the top of the workbook.

Select “Info” and choose “Always Open Read-Only” from “Protect Workbook”.

The excel sheet is now in read-only mode and restricts the file from deleting.

Method 3 – Run a VBA Code to Protect Excel Sheet from Deleting

Step 1:

Place the cursor on the sheet section and right click to open Options.
Go to “View Code”.

The VBA window will open.
Choose your sheet and run the following code-

Private Sub Worksheet_Activate()
ActiveWorkbook.Protect "123"
End Sub
Private Sub Worksheet_Deactivate()
ActiveWorkbook.Unprotect "123"
End Sub

I have put the password “123” in the code.
Press Alt+Q to close the “Microsoft Visual Basic for Applications” window.

Step 2:

Shift to another worksheet and go back to the previous sheet to active the VBA code.
Right-click on the sheet tab and you will see the “Delete” option is gray which means you can not delete the sheet.

Things to Remember

You might also need to unprotect the sheet after protecting the file with a password. To do that go to “Review” and select “Unprotect Sheet”. Enter the password and your sheet will be unprotected.
You can also protect your whole workbook. Go to File > Info > Protect Workbook Structure > Password > OK.

Download Practice Workbook

Protect Sheet from Deleting.xlsm

<< Go Back to Protect Excel Sheet | Excel Protect | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Set WS = Worksheets("Sheet1") Sheet1.Addresses = "" Sheet1.Values = "" For i = 1 To WS.UsedRange.Cells.Count If IsEmpty(WS.UsedRange.Cells(i).Value) = False Then Sheet1.Addresses = Sheet1.Addresses & _ WS.UsedRange.Cells(i).Address & "," Sheet1.Values = Sheet1.Values & WS.UsedRange.Cells(i).Value & "," End If Next i End Sub

Public Addresses As String Public Values As Variant Public NewAddresses As String Public NewValues As String Public IsDone As Boolean Private Sub Worksheet_Change(ByVal Target As Range) Password = "ExcelDemy" Adds = Split(Sheet1.Addresses, ",") Vals = Split(Sheet1.Values, ",") If Target.Value <> "" Then Sheet1.NewAddresses = Sheet1.NewAddresses & _ Target.Address & "*" & Target.Value & "," End If Found1 = False Found2 = False NewValue = "" If Target.Value = "" And Sheet1.IsDone = False Then For i = LBound(Adds) To UBound(Adds) If Adds(i) = Target.Address Then Found1 = True Exit For End If Next i NewAdds = Split(Sheet1.NewAddresses, ",") For i = UBound(NewAdds) - 1 To LBound(NewAdds) Step -1 Arr = Split(NewAdds(i), "*") If Arr(0) = Target.Address Then NewValue = Arr(1) Found2 = True Exit For End If Next i If Found1 = False And Found2 = True Then Target.Value = NewValue UserPassword = InputBox("Enter the Password: ") If UserPassword = Password Then Sheet1.IsDone = True Target.Value = "" Sheet1.IsDone = False Target.Select Else MsgBox ("Invalid Password.") Target.Select End If End If End If End Sub

4 Comments

AJ Mar 9, 2023 at 8:35 AM

I entered the code but I’m getting errors. I don’t know what to do
Wasim Akram Mar 9, 2023 at 12:36 PM

Dear AJ,

Thank you for your response.
There are a few potential issues that could prevent this code from working properly-

1. Make sure that the worksheet module contains the correct event handlers. To do this, right-click on the worksheet tab in Excel and select “View Code”. Then, make sure that you have pasted the code into the correct module, which should be named something like “Sheet1 (Sheet1)”.

2. Check that the password used to protect and unprotect the workbook is correct and does not contain any typos. In this case, the password is set to “”123″”, but you can change this to any other password of your choice.

3. Ensure that the workbook is not already protected by another password. If the workbook is already protected, you may need to unprotect it first before running this code.

If none of the above solutions work, you may need to provide more context or information about the specific error message or issue you are encountering when trying to run this code.

Thanks
Wasim Akram
Exceldemy Team
vishal saini Sep 21, 2023 at 12:39 PM

Hi,
There is a query, can I protect a worksheet in the manner where ,(I can add data but after saving the data ,I cant delete the same Until I don’t save .

If I am going to delete the previous data needs password for deletion.
This help to save my data from mistakenly pressing keys or any changes.
Hope anyone will be understand.
- Reply
  Wasim Akram Sep 25, 2023 at 11:57 AM
  Dear vishal saini,
  Thank you for your response.
  Here I have shared a solution using VBA code to protect a worksheet in the manner you are looking for.
  
  First, opening the VBA window you need to put the following code in the This Workbook section. Don’t forget to change the sheet name according to your sheet.
```
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set WS = Worksheets("Sheet1")
Sheet1.Addresses = ""
Sheet1.Values = ""
For i = 1 To WS.UsedRange.Cells.Count
If IsEmpty(WS.UsedRange.Cells(i).Value) = False Then
Sheet1.Addresses = Sheet1.Addresses & _
WS.UsedRange.Cells(i).Address & ","
Sheet1.Values = Sheet1.Values & WS.UsedRange.Cells(i).Value & ","
End If
Next i
End Sub
```
  Next, insert the below code in your worksheet. You can change the password from the marked section.
```
Public Addresses As String
Public Values As Variant
Public NewAddresses As String
Public NewValues As String
Public IsDone As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Password = "ExcelDemy"
Adds = Split(Sheet1.Addresses, ",")
Vals = Split(Sheet1.Values, ",")
If Target.Value <> "" Then
Sheet1.NewAddresses = Sheet1.NewAddresses & _
Target.Address & "*" & Target.Value & ","
End If
Found1 = False
Found2 = False
NewValue = ""
If Target.Value = "" And Sheet1.IsDone = False Then
For i = LBound(Adds) To UBound(Adds)
If Adds(i) = Target.Address Then
Found1 = True
Exit For
End If
Next i
NewAdds = Split(Sheet1.NewAddresses, ",")
For i = UBound(NewAdds) - 1 To LBound(NewAdds) Step -1
Arr = Split(NewAdds(i), "*")
If Arr(0) = Target.Address Then
NewValue = Arr(1)
Found2 = True
Exit For
End If
Next i
If Found1 = False And Found2 = True Then
Target.Value = NewValue
UserPassword = InputBox("Enter the Password: ")
If UserPassword = Password Then
Sheet1.IsDone = True
Target.Value = ""
Sheet1.IsDone = False
Target.Select
Else
MsgBox ("Invalid Password.")
Target.Select
End If
End If
End If
End Sub
```
  Finally, you can protect your worksheet in the manner where you can add data and after saving the data you can’t delete the same until you don’t save.
  
  Thanks
  Wasim Akram
  Exceldemy Team

How to Protect Excel Sheet from Deleting (3 Simple Methods)

Method 1 – Use Password to Protect Excel Sheet from Deleting

Method 2 – Activate Read Only Mode to Protect Excel Sheet from Deleting

Method 3 – Run a VBA Code to Protect Excel Sheet from Deleting

Things to Remember

Related Articles

Leave a reply Cancel reply