Getting Run-time Error on Macro when Workbook is Protected

[klynn714]

I am getting a run-time error '1004' Method 'Visible' of object'_Worksheet' failed when running a macro. I have to have the workbook protected to keep users from deleting sheets. The code works great when the workbook is unprotected. I had to change it workbook_deactivate instead of workbook_beforeclose because I already had a beforeclose event, unless someone can help me combine them into beforeclose. I'm fairly new to VBA. Can someone please help? The following is my code in ThisWorkbook:

Private Sub Workbook_Open()Application.ScreenUpdating = False
Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
  Select Case ws.Name
  Case "Lists", "Calendar"
  ws.Visible = xlSheetVeryHidden
  Case Else: ws.Visible = xlSheetVisible
  End Select
  Next ws
  Sheets("START").Visible = xlVeryHidden
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Deactivate()Application.ScreenUpdating = False
Dim ws As Worksheet
  Sheets("START").Visible = xlSheetVisible
  For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "START" Then
  ws.Visible = xlVeryHidden
  End If
  Next ws
  ActiveWorkbook.Save
  Application.ScreenUpdating = True


End Sub

I'm not sure if this is relevant but I also have the following codes in addition for other actions that I need:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Excel.Range)
 
Select Case Application.CutCopyMode
 Case Is = False
 'do nothing
 Case Is = xlCopy
 'do nothing
 Case Is = xlCut
 MsgBox "Please DO NOT Cut and Paste. Use Copy and Paste; then delete the source."
 Application.CutCopyMode = False 'clear clipboard and cancel cut
 End Select
 
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)    Dim msg As String
    If Intersect(Target, Range("b4:b205")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then
        With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
        End With
        Exit Sub
    End If
    If Target.Value <> "" Then
        If MatchAll(Target, Sh, [b4:b205]) <> "" Then
            msg = "You have already added that inquiry " & MatchAll(Target, Sh, [b4:b205])
        Else
            If Target.Value Like "*[!0-9]*" Then
                msg = "-Numbers only"
            Else
                If Len(Target.Value) < 12 Then msg = "-Must have " & _
                "12 characters or more" & vbLf
            End If
        End If
        If Len(msg) Then
            MsgBox "Invalid entry" & vbLf & Target.Value & ";" & _
            String(2, vbLf) & msg, , Target.Address(0, 0)
            Application.EnableEvents = False
            Target.ClearContents: msg = ""
            Application.EnableEvents = True
        End If
    End If
End Sub

Function MatchAll(r, Sh, Rng) As String
    Dim txt, a, i As Long, ws As Worksheet, x, e
    a = Rng.Value
    For Each ws In Worksheets
        x = Filter(ws.Evaluate("transpose(if(" & Rng.Address & "=" & r.Address(external:=True) & _
        ",row(" & Rng.Row & ":" & Rng(Rng.Count).Row & "),char(2)))"), Chr(2), 0)
        If ws Is Sh Then
            If UBound(x) > 0 Then
                For Each e In x
                    If e <> CStr(r.Row) Then MatchAll = ws.Name & "!B" & e: Exit Function
                Next
            End If
        Else
            If UBound(x) > -1 Then MatchAll = ws.Name & "!B" & x(0): Exit Function
        End If
    Next
End Function

Private Sub Workbook_BeforeClose(Cancel As Boolean)    Dim ws As Worksheet, r As Range, r2 As Range
    For Each ws In Worksheets
        Set r = ws.Range("J6:J16")
        For Each r2 In r.Cells
            If r2.Value = "Hours Needed" Then
                MsgBox ws.Name & " " & r2.Address & " Production Hours are Required"
                Application.GoTo ws.Range(r2.Address).Offset(, -1)
                Cancel = True
            End If
        Next r2
    Next ws
End Sub

Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
     Cancel As Boolean)
     Dim lReply As Long
     If SaveAsUI = True Then
    lReply = MsgBox("Sorry, you are not allowed to save this " & _
     "workbook as another name. Do you wish to save this " & _
     "workbook?", vbQuestion + vbOKCancel)
     Cancel = (lReply = vbCancel)
     If Cancel = False Then Me.Save
     Cancel = True
     End If
    End Sub

 

[mumps]

Have you tried including an 'unprotect' line of code at the beginning of your macro and a 'protect' line at the end?

For Each ws In Sheets
    ws.Unprotect
    'your code here
    ws.Protect
Next ws

 

[klynn714]

Sorry since I'm new to VBA, I'm not sure where I need to add the unprotect and protect code. Do I need to add them both to the workbook_open and workbook_deactivate?

 

[klynn714]

Actually that worked! Thank you so much! I really appreciate it

 

[mumps]

You are very welcome.