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

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, you might need to protect your file from deletion. After completing a file you may send it to viewers and you don’t want the viewers to make any unintentional changes to the file. Excel does have some built-in features to protect your excel sheet from deleting data or cells with formulas.

Table of Contents hide

How to Protect Excel Sheet from Deleting: 3 Quick Methods

1. Use Password to Protect Excel Sheet from Deleting

2. Activate Read Only Mode to Protect Excel Sheet from Deleting

3. Run a VBA Code to Protect Excel Sheet from Deleting

Things to Remember

Conclusion

How to Protect Excel Sheet from Deleting: 3 Quick Methods

In this article, I will share 3 simple methods to protect excel sheets from deleting.

Suppose we have a dataset of some Products and their sales date-wise. Now we are going to protect this worksheet from deleting.

1. Use Password to Protect Excel Sheet from Deleting

To protect the excel sheet from deleting the common and simple method is to protect user passwords. You can save a password for the sheet to protect it from deleting. Follow the steps-

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.
Type the same password you typed in the previous window.
Click OK.

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

2. Activate Read Only Mode to Protect Excel Sheet from Deleting

When you want to restrict your file from deleting you may apply the read-only mode from the built-in tools option. In this method, I will show you a simple method to protect your excel sheet by applying read-only mode.

Steps:

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

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

Thus you can make your excel sheet to read-only mode and restrict the file from deleting.

Similar Readings

3. Run a VBA Code to Protect Excel Sheet from Deleting

You can also run a VBA code to protect the excel sheet.

Step 1:

Put the cursor on the sheet section and click the right button of the mouse to open Options.
From the options go to “View Code”.

The VBA window will open.
In the new window, choose your sheet and then run the following code-

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

As you can see I have put the password “123” in the code. You can choose your own password and put it in the code section.
Press Alt+Q to close the “Microsoft Visual Basic for Applications” window.

Step 2:

Now 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.

So, applying this VBA code we have successfully protected our sheet from deleting.

Similar Readings:

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”. Now type 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

Download this practice workbook to exercise while you are reading this article.

Protect Sheet from Deleting.xlsm

Conclusion

In this article, I have shown some easy methods to protect excel sheets from deleting. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We are always responsive to your queries. Stay tuned and keep learning.

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)

How to Protect Excel Sheet from Deleting: 3 Quick Methods

1. Use Password to Protect Excel Sheet from Deleting

2. Activate Read Only Mode to Protect Excel Sheet from Deleting

3. Run a VBA Code to Protect Excel Sheet from Deleting

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply