VBA Event - Prevent a Worksheet from being deleted.

[djones05]

Hi,

I am struggling on how I can protect the Worksheet of my Workbook. In my Workbook, I have three worksheets namely, "Controls" and "SourceX".

The "SourceX" worksheet is hidden, so it is not currently my concern. My issue is with the worksheet "Controls", which I need to protect at all costs. It contains all the menu controls, with buttons, for different functionalities. I have tried to use the following VBA events for testing which one would work:

(1) "Workbook_SheetBeforeDelete(ByVal Sh As Object)" event from "Thisworkbook"
(2) "Worksheet_BeforeDelete()" event from the "Controls" sheet

Here are my codes for both approaches:

================================================================
(1) Workbook_SheetBeforeDelete

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)

Dim shPwd As String
Dim inPss As String
Dim shName As String

shName = "Controls"

If Sh.Name = shName Then
shPwd = "qwerty"
inPss = InputBox("Enter the password to delete the sheet")
If inPss = shPwd Then
'If the password is correct, allow the deletion
Else
'If the password is incorrect, prevent the deletion
MsgBox "Invalid credential. The sheet will not be deleted.", vbExclamation
Cancel = True 'Terminate the sheet deletion
End If
End If

End Sub
-----------------------------------------------------------------------------------------------------------

(2) Worksheet_BeforeDelete()

Private Sub Worksheet_BeforeDelete()

Dim shPwd As String
Dim inPss As String

shPwd = "qwerty"

If inPss = shPwd Then
'If the password is correct, allow the deletion
Else
'If the password is incorrect, prevent the deletion
MsgBox "Invalid credential. The sheet will not be deleted.", vbExclamation
Cancel = True 'Terminate the sheet deletion
End If

End Sub
================================================================

For some reason, these codes don't work. it complains about the "Cancel = True" statement and the code displays an error, and when terminated, it deletes the "Controls" sheet. I have even used "Sh.Delete" in replacement for "Cancel = True", but it just didn't work. Even if I tried to put an incorrect password, it loops in twice with the confirmation of deleting the sheet, and then an error window will appear. Pressing Debug or End, still deletes the "Controls" sheet.

So, I am reaching out to you, so you can give me some advise on how to resolve this or provide a different approach to prevent in deleting the said sheet.

Thank you very much!

 

[mumps]

Try this macro in the "Controls" worksheet code module. It will protect the workbook when the sheet is activated and unprotect it when the sheet is de-activated by selecting a different sheet.

Private Sub Worksheet_Activate()
    ThisWorkbook.Protect "qwerty"
    Dim shPwd As String, inPss As String
    shPwd = "qwerty"
    If MsgBox("Do you wish to delete the 'Controls' sheet", vbYesNo) = vbYes Then
        inPss = InputBox("Enter the password to delete the sheet")
        If inPss = shPwd Then
            ThisWorkbook.Unprotect "qwerty"
        Else
            ThisWorkbook.Protect "qwerty"
            MsgBox "Invalid credential. The sheet will not be deleted.", vbExclamation
        End If
    End If
End Sub

 

[djones05]

Try this macro in the "Controls" worksheet code module. It will protect the workbook when the sheet is activated and unprotect it when the sheet is de-activated by selecting a different sheet.

Private Sub Worksheet_Activate()
    ThisWorkbook.Protect "qwerty"
    Dim shPwd As String, inPss As String
    shPwd = "qwerty"
    If MsgBox("Do you wish to delete the 'Controls' sheet", vbYesNo) = vbYes Then
        inPss = InputBox("Enter the password to delete the sheet")
        If inPss = shPwd Then
            ThisWorkbook.Unprotect "qwerty"
        Else
            ThisWorkbook.Protect "qwerty"
            MsgBox "Invalid credential. The sheet will not be deleted.", vbExclamation
        End If
    End If
End Sub

Hi mumps,

Thank you so much for your response and for providing a solution. However, when I have applied this to "Workbook_Activate" event, it immediately prompts me if I wanted to delete the sheet. Then, I have tried to use it in "Workbook_SheetBeforeDelete" event, it did not prompt for attention.

However, it prevents me from creating a new sheet, since it has protected the entire workbook. I will still try to use your code in different approach and see which one would work best.

The idea is protecting the "Controls" worksheet from being deleted, but still allows the creating, deletion and modification of other worksheets.

Thank you so much again, @mumps !

 

[mumps]

My apologies. I forgot to include the Worksheet_Deactivate macro. The two macros must be placed into the worksheet code module. Do the following: right click the tab name for your "Controls" sheet and click 'View Code'. Paste the macros into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Activate()
    ThisWorkbook.Protect "qwerty"
    Dim shPwd As String, inPss As String
    shPwd = "qwerty"
    If MsgBox("Do you wish to delete the 'Controls' sheet", vbYesNo) = vbYes Then
        inPss = InputBox("Enter the password to delete the sheet")
        If inPss = shPwd Then
            ThisWorkbook.Unprotect "qwerty"
        Else
            ThisWorkbook.Protect "qwerty"
            MsgBox "Invalid credential. The sheet will not be deleted.", vbExclamation
        End If
    End If
End Sub

Private Sub Worksheet_Deactivate()
    ThisWorkbook.Unprotect "qwerty"
End Sub

still allows the creating, deletion and modification of other worksheets.

If you want to do this, just activate any other sheet and the workbook will be unprotected.

 

[Joe4]

Just a note that any sheet can still easily be deleted.
All the user has to do is open the workbook with VBA/Macros disabled, and none of this code will run, so they will be able to delete any sheet.

 

[djones05]

My apologies. I forgot to include the Worksheet_Deactivate macro. The two macros must be placed into the worksheet code module. Do the following: right click the tab name for your "Controls" sheet and click 'View Code'. Paste the macros into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Activate()
    ThisWorkbook.Protect "qwerty"
    Dim shPwd As String, inPss As String
    shPwd = "qwerty"
    If MsgBox("Do you wish to delete the 'Controls' sheet", vbYesNo) = vbYes Then
        inPss = InputBox("Enter the password to delete the sheet")
        If inPss = shPwd Then
            ThisWorkbook.Unprotect "qwerty"
        Else
            ThisWorkbook.Protect "qwerty"
            MsgBox "Invalid credential. The sheet will not be deleted.", vbExclamation
        End If
    End If
End Sub

Private Sub Worksheet_Deactivate()
    ThisWorkbook.Unprotect "qwerty"
End Sub

If you want to do this, just activate any other sheet and the workbook will be unprotected.

Hi @mumps,

Thank you so much for your help. This would do just fine. I can just add a password to the VB environment so, no other user can get into the code.

Thank you!

 

[mumps]

You are very welcome.

 

[djones05]

Just a note that any sheet can still easily be deleted.
All the user has to do is open the workbook with VBA/Macros disabled, and none of this code will run, so they will be able to delete any sheet.

Hi @Joe4 ,

Thank you so much for your advice. Will keep this in mind.

 

[ajsobie]

Hi,

I am struggling on how I can protect the Worksheet of my Workbook. In my Workbook, I have three worksheets namely, "Controls" and "SourceX".

The "SourceX" worksheet is hidden, so it is not currently my concern. My issue is with the worksheet "Controls", which I need to protect at all costs. It contains all the menu controls, with buttons, for different functionalities. I have tried to use the following VBA events for testing which one would work:

(1) "Workbook_SheetBeforeDelete(ByVal Sh As Object)" event from "Thisworkbook"
(2) "Worksheet_BeforeDelete()" event from the "Controls" sheet

Here are my codes for both approaches:

================================================================
(1) Workbook_SheetBeforeDelete

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)

Dim shPwd As String
Dim inPss As String
Dim shName As String

shName = "Controls"

If Sh.Name = shName Then
shPwd = "qwerty"
inPss = InputBox("Enter the password to delete the sheet")
If inPss = shPwd Then
'If the password is correct, allow the deletion
Else
'If the password is incorrect, prevent the deletion
MsgBox "Invalid credential. The sheet will not be deleted.", vbExclamation
Cancel = True 'Terminate the sheet deletion
End If
End If

End Sub
-----------------------------------------------------------------------------------------------------------

(2) Worksheet_BeforeDelete()

Private Sub Worksheet_BeforeDelete()

Dim shPwd As String
Dim inPss As String

shPwd = "qwerty"

If inPss = shPwd Then
'If the password is correct, allow the deletion
Else
'If the password is incorrect, prevent the deletion
MsgBox "Invalid credential. The sheet will not be deleted.", vbExclamation
Cancel = True 'Terminate the sheet deletion
End If

End Sub
================================================================

For some reason, these codes don't work. it complains about the "Cancel = True" statement and the code displays an error, and when terminated, it deletes the "Controls" sheet. I have even used "Sh.Delete" in replacement for "Cancel = True", but it just didn't work. Even if I tried to put an incorrect password, it loops in twice with the confirmation of deleting the sheet, and then an error window will appear. Pressing Debug or End, still deletes the "Controls" sheet.

So, I am reaching out to you, so you can give me some advise on how to resolve this or provide a different approach to prevent in deleting the said sheet.

Thank you very much!

I wrote logic that just renames the deleted sheet, copies and creates the renamed sheet, renames the copy to the original name and generates a message for the user.

Private Sub Worksheet_BeforeDelete()
Dim ws As Worksheet

Application.EnableEvents = False

Set ws = ThisWorkbook.Worksheets("Insert Name Here")
ws.Name = "Delete Cancelled"
ws.Copy After:=Sheets("Delete Cancelled")
Set ws = ThisWorkbook.Worksheets("Delete Cancelled (2)")
ws.Name = "Insert Name Here"

MsgBox ("The ""Insert Name Here"" sheet cannot be deleted." & vbNewLine & vbNewLine & "Delete Cancelled.")

Application.EnableEvents = True

End Sub