如何使用循环锁定所有现有工作表中的单元格区域

Question

我有一个现有的工作簿，它有50多个工作表。我需要锁定每个现有工作表的单元格范围（b7:b51）。我试着用循环来做这件事，我有一个循环代码，它确实贯穿了所有的工作表，我需要放正确的代码来锁定单元格。

   Sub WorksheetLoop()
     Dim WS_Count As Integer
     Dim I As Integer
     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
     WS_Count = ActiveWorkbook.Worksheets.Count
     ' Begin the loop.
     For I = 2 To WS_Count
   ActiveSheet.range("B1:B51").locked=true. --this is not correct.

    MsgBox ActiveWorkbook.Worksheets(I).Name

     Next I
  End Sub

感谢

Answer 1

试试这个。。。

Sub WorksheetLoop()
 Dim WS_Count As Integer
 Dim I As Integer
 ' Set WS_Count equal to the number of worksheets in the active
 ' workbook.
 WS_Count = ActiveWorkbook.Worksheets.Count
 ' Begin the loop.
 For I = 1 To WS_Count
If Worksheets(I).Range("C1:C51").Locked <> True Then
  Worksheets(I).Range("C1:C51").Locked = True
  Worksheets(I).Protect Contents:=True
Else
End If
MsgBox ActiveWorkbook.Worksheets(I).Name

 Next I
End Sub

Answer 2

这样就可以了。

Sub Macro1()
Dim WS_Count As Integer
     Dim I As Integer
     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
     WS_Count = ActiveWorkbook.Worksheets.Count
     ' Begin the loop.
     For I = 2 To WS_Count
        Dim sheet As Worksheet
        Set sheet = Sheets(I)
        sheet.Unprotect
        sheet.UsedRange.Locked = False
        sheet.Range("B7:B51").Locked = True
        sheet.Protect Contents:=True
        MsgBox ActiveWorkbook.Worksheets(I).Name
     Next I
End Sub

Answer 3

Public Sub ProtectRange()
Dim i As Integer, wsCount As Integer
wsCount = ActiveWorkbook.Worksheets.Count
For i = 1 To wsCount
    ActiveWorkbook.Worksheets(i).Range("B1:B51").Locked = True
    ActiveWorkbook.Worksheets(i).Protect Contents:=True
Next i
End Sub