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

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

Activate Read Only Mode to Protect Excel Sheet from Deleting

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

Read More: Protect Excel Sheet but Allow Data Entry


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

Run a VBA Code to Protect Excel Sheet from Deleting

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

Run a VBA Code to Protect Excel Sheet from Deleting

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


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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