How to prevent a sheet from being deleted using VBA?

To prevent a worksheet in Excel from being deleted using VBA, you can take a few different approaches. Although there’s no direct method in VBA to prevent sheet deletion itself, you can implement a workaround by intercepting the attempt to delete it and using password protection to prevent changes to workbook or sheet structure. Here’s a strategy you can use:

  • Protect the Workbook Structure:

Protect the workbook structure to prevent the addition, deletion, or renaming of sheets. This is a straightforward method to prevent sheet deletion.

   Sub ProtectWorkbook()
       ' Protecting the workbook structure
       ThisWorkbook.Protect Password:="yourPassword", Structure:=True
   End Sub
  • Intercept Sheet Deletion:

While you can’t directly intercept a sheet deletion in progress, you can use events to monitor and respond to changes that suggest a sheet deletion attempt.

  • Workbook-Specific Macros to Undo Unwanted Deletions:

In the case where sheets could get deleted (if the workbook structure isn’t protected because you want to allow other changes), you could implement a way to track sheet deletions and re-add the deleted sheet:

Although there’s no direct “SheetDelete” event, you can use `Workbook_SheetDeactivate` or `Workbook_SheetChange` events as part of a strategy to indirectly detect changes.

  • Handle Events Effectively (if appropriate):

In the workbook’s ThisWorkbook module, you could leverage certain events, though as mentioned, there’s no direct VBA sheet deletion prevention:

   Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
       Dim ws As Worksheet
       Dim sheetExists As Boolean
       sheetExists = False

       ' Check if a specific sheet exists, imply deletion otherwise
       For Each ws In ThisWorkbook.Sheets
           If ws.Name = "SheetNameYouWantToProtect" Then
               sheetExists = True
               Exit For
           End If
       Next ws

       ' Notify or restore if it doesn't exist anymore
       If Not sheetExists Then
           MsgBox "Protected sheet was deleted!", vbExclamation
           ' Optionally, you can re-add the sheet or perform another action
           ' ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "SheetNameYouWantToProtect"
       End If
   End Sub
  • Recovery or Restoration:

Allow for easy restoration of important sheets as soon as they’re deleted, if practical.

Remember, these are indirect methods. The best way to ensure sheets aren’t deleted is through structural protection of the workbook itself. Adjust these strategies according to your specific project needs and security considerations.

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project