Protect workbook structure vs prevention of worksheets

[Eric Carolus]

Good Day folks on this Merry Christmas Day

For the last couple of days now I had tried in vain to protect some
sheets from deletion (in the same workbook) while others, after being copied from a
template should be prevented from deletion/protected from deletion.

The workbook contains 4 sheets that the user should not be able to delete. The sheet called "Master" is a template and must always be available.
The user should not be able to delete it. The sheet called Add _Person contains a dynamic list called 'ListNames'. These names are used to to name the sheets
after an employee (and is functional). My main problem is protecting some sheets from deletion while others should not be deleted
by the user.

I have tried numerous strategies
Private Sub Workbook_Open()
Me.Worksheets("Sheet1").Protect Password:="myPassword", UserInterfaceOnly:=True Private Sub Worksheet_Activate()
ThisWorkbook.Protect Password:="password", Structure:=True
End Sub
OR
Private Sub Worksheet_Deactivate()
ThisWorkbook.Unprotect Password:="password"
End Sub

Me.Worksheets("Sheet2").Protect Password:="myPassword", UserInterfaceOnly:=True
Me.Worksheets("Sheet3").Protect Password:="myPassword", UserInterfaceOnly:=True
End Sub

Your help will surely be appreciated.

Thank you
Ps. I include a copy of the workbook to further elucidate my problem.

 

[Fluff]

How about

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Select Case Sh.name
        Case "Master", "Add_Person", "Help", "Info"
            ThisWorkbook.Protect "Pword"
    End Select
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    ThisWorkbook.Unprotect "Pword"
End Sub

These need to go in the ThisWorkbook module.
This will not prevent sheets from being deleted via code.

 

[Eric Carolus]

Hi Fluff

Thanks for the speedy reply. The sheets
"Master", "Add_Person", "Help", "Info" are fully protected.

The problem now is that when I copy the sheet "Master" (the template) and rename the resulting
copy via the combo box selection, the resulting teacher cannot be deleted.
And I wish for the user to delete those sheets if required.

Any other ideas to to work around that problem!

Thanks again.

Crow

 

[Fluff]

Add this line to the code as shown

'COPY THE SHEET CALLED MASTER
        ThisWorkbook.Unprotect "Pword"
        Worksheets("Master").Copy after:=Sheets(Worksheets.Count)

 

[Eric Carolus]

Hi Fluff

I have sort stumbled onto the solution!

I simply added:
ThisWorkbook.Unprotect Password:="myPassword"
BEFORE

Worksheets("Master").Copy after:=Sheets(Worksheets.Count)

Now ALL sheets copied from the template can be deleted and thanks to your tip
the sheets "Master", "Add_Person", "Help", "Info" are still fully protected.

Thanks a lot Fluff for your support.
Could not have done it without you!

Eric

 

[Fluff]

You could also slim down the cmdadd code like

Private Sub cmdadd_Click()

'IF NO SELECTION HAD NOT BEEN MADE, THEN INFORM THE USER AS SUCH
    If Me.cboselect.Value = "" Or Me.cboselect.Value = "Select an Employee" Then
        MsgBox "   Please be so kind and select an Employee" _
        & vbCrLf _
        & "                from the dropdown list." _
        & vbCrLf _
        & vbCrLf _
        & vbCrLf _
        & "                 -------------------------" _
        & vbCrLf _
        & "                        Thank You." _
        & vbCrLf _
        & "                 -------------------------", vbInformation, "                        S E L E C T   AN   E D U C A T O R"
       
        Exit Sub
    End If


'IF THE SHEET FOR THE EDUCATOR EXISTS, THEN INFORM THE USER SO
    If Evaluate("isref('" & Me.cboselect.Value & "'!A1)") Then
            
        MsgBox "         The Sheet for the Employee " & "'" & frmNames.cboselect.Text & "'" & " exists." _
         & vbCrLf _
         & vbCrLf _
         & vbCrLf _
         & "             Please select a different Employee." _
         & vbCrLf _
         & vbCrLf _
         & "                   --------------------------" _
         & vbCrLf _
         & "                           Thank You." _
         & vbCrLf _
         & "                   --------------------------", vbInformation, "            E D U C A T O R     E X I S T S"
        Exit Sub
    End If

 'IF A SELECTION HAD BEEN MADE

Application.ScreenUpdating = False

On Error Resume Next

'COPY THE SHEET CALLED MASTER
        ThisWorkbook.Unprotect "Pword"
        Worksheets("Master").Copy after:=Sheets(Worksheets.Count)
        
        On Error Resume Next

'PUT THE SHEET NAME EQUAL TO TEXT/VALUE SELECTED FROM THE COMBO BOX
        ActiveSheet.name = Me.cboselect.Value

' VERY IMPORATNT PIECE OF CODE!!!!! RESET THE COMBO BOX TEXT/VALUE TO EMPTY
    frmNames.cboselect.ListIndex = -1



Application.ScreenUpdating = False

    Unload Me




'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
End Sub