How to Prevent Users from Deleting Excel Worksheets with VBA
Question details
The user needs to stop collaborators from deleting Excel worksheets, including newly created ones, using VBA because standard workbook structure protection interferes with their necessary workflow.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Collaborating on a shared workbook where critical sheets must remain intact, but users still need enough freedom to perform tasks that standard structure protection normally blocks.
- Observed behavior
- Without protection, users can accidentally or intentionally delete essential worksheets. Standard protection blocks too many actions, necessitating a dynamic VBA-based workaround.
Always test VBA macros in a blank copy of your workbook first to avoid unintended data loss, and remember that VBA solutions only function if the user enables macros upon opening the file.
Use ThisWorkbook Events to Toggle Protection
Deploy a combination of workbook event macros to automatically protect the workbook structure when users navigate away from a sheet, and delete any newly created unauthorized sheets.
Since Excel does not have a native Worksheet_BeforeDelete event, you can simulate this protection by leveraging the Workbook_SheetActivate and Workbook_SheetDeactivate events. This ensures the workbook structure is dynamically locked to prevent deletions when necessary.
Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) Editor.
In the Project Explorer pane on the left side of the window, locate your active workbook and double-click on 'ThisWorkbook'.
Copy and paste the following code into the blank module window: Dim newSh As Boolean Private Sub Workbook_NewSheet(ByVal Sh As Object) Application.DisplayAlerts = False newSh = True Sh.Delete Application.DisplayAlerts = True End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) Me.Unprotect End Sub Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) If newSh Then newSh = False: Exit Sub Me.Protect End Sub
Go to File > Save As and ensure you save the file as an Excel Macro-Enabled Workbook (.xlsm) so the code functions properly.
Apply Built-in Workbook Structure Protection
If VBA restrictions are bypassed or deemed too complex for standard users, the native Protect Workbook feature serves as the most reliable fallback.
Protect Your Worksheets with WPS Spreadsheet
WPS Spreadsheet offers robust support for VBA macros alongside intuitive built-in workbook protection, allowing you to secure your data and prevent unwanted sheet deletions effortlessly.
- 1. Open Your Macro-Enabled File: Launch WPS Spreadsheet and open your existing .xlsm workbook.
- 2. Access the Developer Tools: Click on the 'Developer' tab on the ribbon and select 'VB Editor' to open the macro workspace.
- 3. Apply Security Macros: Double-click 'ThisWorkbook' in the Project pane, paste your sheet protection code, and save the document.
- 4. Use Native Protection: For a non-macro alternative, go to the 'Review' tab and click 'Protect Workbook' to instantly lock the file structure.
Frequently Asked Questions
Can users bypass the VBA code to delete worksheets?
Yes. If a user chooses not to enable macros when opening the Excel file, or changes their Trust Center settings to block macros entirely, the VBA code will not execute. This leaves the worksheets unprotected and vulnerable to deletion.
Does Excel have a specific Worksheet_BeforeDelete event?
No, Excel does not feature a native Worksheet_BeforeDelete event. Developers typically rely on workarounds, such as using the Workbook_SheetDeactivate event to protect the workbook, or employing custom ribbon controls to intercept the delete action.
How do I stop users from deleting specific sheets but allow them to delete others?
You can modify your VBA event handlers to check the name of the active or deactivated sheet. By adding an 'If' statement that checks the sheet name (e.g., If Sh.Name = "CriticalData"), you can trigger workbook protection only when specific sheets are involved, leaving the rest editable.
Why use VBA instead of standard Workbook Protection?
Standard Workbook Protection locks the entire structure, meaning users cannot insert, move, hide, or unhide any sheets at all. VBA allows for more dynamic rules, enabling users to perform necessary daily tasks while strictly monitoring or preventing deletions in the background.
