VBA Sheet Protection Failing

[stuartgb100]

Hi,

Code sometimes works, then doesn't.

I have marked the two lines giving problems as line1 and line2.

The error stated is Run Time 1004. Method 'Range' of object'_Worksheet' failed

Option Explicit


Sub ProtectAllSheets()


Dim sh As Worksheet, myPassword1 As String
Dim myPassword2 As String, FoundTotal As Range
Dim FoundQty As Range


  myPassword1 = "qwerty"
  myPassword2 = "123"
  ' Security & protection only, from this point onwards.
  ' Lock ranges etc.
  ' To set cells to be protected:   set Locked status to True
  ' First, remove all protection
  For Each sh In ThisWorkbook.Worksheets
    If sh.Name = "Basic Master" Then
        sh.Unprotect Password:=myPassword1
        sh.Visible = xlSheetVisible
    Else
        sh.Unprotect Password:=myPassword2
    End If
    
    sh.Cells.Locked = False
    
   ' Find the cell containing "Total"
   Set FoundTotal = sh.Range("A1:ZZ3").Find _
       (What:="Total", LookAt:=xlWhole, MatchCase:=False)
   ' Find the cell containing "Qty"
   Set FoundQty = sh.Range("A1:ZZ3").Find(What:="Qty")
   ' Lock the first 3 header rows up to "Total"
   sh.Range(Cells(1, 1), Cells(3, FoundTotal.Column)).Locked = True      ' Line1
   ' Unlock the Type and Type Nr cells.
   sh.Range(Cells(2, 3), Cells(3, FoundQty.Column - 1)).Locked = False   'Line2
   ' Unlock the Insert/Delete Column cells
   sh.Cells(1, FoundTotal.Column).Locked = False
   sh.Cells(1, FoundTotal.Column - 1).Locked = False
   sh.EnableOutlining = True
   'Application.ScreenUpdating = True
   Application.EnableEvents = True
   Application.DisplayAlerts = True
   sh.Columns("A").Application.DisplayAlerts = False
   sh.Columns(FoundTotal.Column - 1).Application.DisplayAlerts = False
   sh.Columns(FoundTotal.Column).Application.DisplayAlerts = False
   Application.ErrorCheckingOptions.UnlockedFormulaCells = True
   sh.EnableSelection = xlUnlockedCells
   If sh.Name = "Basic Master" Then
       sh.Protect Password:=myPassword1, UserInterFaceOnly:=True, _
           AllowInsertingRows:=True, AllowDeletingRows:=True, _
           AllowFormattingCells:=True, AllowFormattingColumns:=True, _
           AllowFormattingRows:=True
'       .EnableSelection = xlUnlockedCells
   Else
       sh.Protect Password:=myPassword2, UserInterFaceOnly:=True, _
           AllowInsertingRows:=True, AllowDeletingRows:=True, _
           AllowFormattingCells:=True, AllowFormattingColumns:=True, _
           AllowFormattingRows:=True, AllowInsertingColumns:=True
   End If
   
   Application.ScreenUpdating = False
   Application.EnableEvents = False
   Application.DisplayAlerts = False
    
  Next


  Sheet1.Visible = xlSheetVeryHidden


End Sub

I have tried the above using a With construct and a similar error occurred.

I'd be grateful to know what is wrong, and why, please.

Regards.

[Bob Phillips]

Try

        sh.Range(sh.Cells(1, 1), sh.Cells(3, FoundTotal.Column)).Locked = True ' Line1
         ' Unlock the Type and Type Nr cells.
        sh.Range(sh.Cells(2, 3), sh.Cells(3, FoundQty.Column - 1)).Locked = False 'Line2
         ' Unlock the Insert/Delete Column cells

or

        With sh
        .Range(.Cells(1, 1), .Cells(3, FoundTotal.Column)).Locked = True ' Line1
         ' Unlock the Type and Type Nr cells.
        .Range(.Cells(2, 3), .Cells(3, FoundQty.Column - 1)).Locked = False 'Line2
         ' Unlock the Insert/Delete Column cells
End With

[stuartgb100]

xld, many thanks.

That solved it.

Regards.