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.


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.

Protect Excel Sheet 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”.

Use Password to Protect Excel Sheet from Deleting

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

Use Password to Protect Excel Sheet from Deleting

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

Activate Read Only Mode to Protect Excel Sheet from Deleting

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

Read More: Protect Excel Sheet but Allow Data Entry


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

Run a VBA Code to Protect Excel Sheet from Deleting

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

Run a VBA Code to Protect Excel Sheet from Deleting

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.


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.


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

4 Comments
  1. I entered the code but I’m getting errors. I don’t know what to do

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

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

    • 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

      Protect Sheet from Deleting-1

      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

      Protect Sheet from Deleting-2

      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo