Lock cells in specific worksheets

user_17664038 0
Viewed 61

I am trying to lock a range within specific worksheets in a workbook. The code I use only locks the first worksheet's cells and doesn't loop onto the next one locking the range.

Sub lockpivots()

Dim sh As Worksheet

For Each sh In Sheets

Select Case sh.Name
Case Is = "2) Review Charts-Management", "3) Review Charts-Functional"

    Selection.Locked = False
    Selection.FormulaHidden = False
    range("b2:C9").Select
    ActiveSheet.Protect Password:="IDR"
    
    End Select
    
    Next sh
    
End Sub
password-protection vba excel
2 Answers

Don't use Selection or .Select or ActiveSheet, rather refer to sh:

For Each sh In ThiseWorkbook.Worksheets 
    Select Case sh.Name
        Case "2) Review Charts-Management", "3) Review Charts-Functional"
            With sh
                .Range("B2:C9").Locked = False
                .Range("B2:C9").FormulaHidden = False
                .Protect Password:="IDR"
            End With
    End Select
Next sh
user_92458530

Locking Cells of a Range

Option Explicit

Sub LockPivots()

    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
    
        Select Case ws.Name
        Case "2) Review Charts-Management", "3) Review Charts-Functional"
            ws.UnProtect Password:="IDR"
            With ws.Range("B2:C9")
                .Locked = False
                .FormulaHidden = False
            End With
            ws.Protect Password:="IDR"
        Case Else ' do nothing
        End Select

    Next ws
    
End Sub
user_98140690
Related