PDA

View Full Version : [SOLVED] VBA Sheet Protection Failing



stuartgb100
02-10-2016, 11:30 AM
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
02-10-2016, 11:56 AM
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
02-10-2016, 12:50 PM
xld, many thanks.

That solved it.

Regards.