循环遍历列并锁定该行(如果存在任何值)

Question

我是 VBA 的新手，正在寻找能够遍历列并在单元格中存在值时锁定行的示例代码。

Answer 1

以下是编码时的一些建议：

1. 使用显式选项，这样您就不会遇到未定义变量的意外行为
2. 使用最接近的对应类型定义变量(例如，cl是代码中的Range，您将其定义为可能工作的对象，但您在其他功能中丢失了智能感知(
3. 始终缩进代码(请参阅 www.rubberduckvba.com 可帮助您处理数据的免费工具(
4. 对于前一个，只是一个品味问题，但在单独的行中定义每个变量，将帮助您保持代码井井有条
5. 尝试分离定义变量的逻辑并重用它们
6. 除非绝对必要，否则避免使用 select

查看并自定义代码，使其符合您的需求。

您可以通过按F8并逐行执行来查看代码中发生的情况。

法典：

Public Sub test()
Dim targetSheet As Worksheet
Dim targetRange As Range
Dim targetRow As Range
Dim sheetPassword As String
Dim lastRow As Long
Dim lockedResult As Boolean
Set targetSheet = ThisWorkbook.Worksheets("Sheet1")
sheetPassword = "Test"
' Remove protection
targetSheet.Unprotect Password:=sheetPassword
' >>>>>Remove cells protection to whole sheet<<<<<<<
targetSheet.Cells.locked = False
'Find last row in column B (that's what the 2 means after Count,)
lastRow = targetSheet.Cells(targetSheet.Rows.Count, 2).End(xlUp).Row
' Set the evaluated range
Set targetRange = targetSheet.Range("B1:B" & lastRow)
For Each targetRow In targetRange.Cells
' Check the first cell (that's what the 1 means after (,)
Select Case targetRow.Cells(, 1).Value
Case "TOP LEVEL", "No", "Maybe so" ' All of these cases are OR as they are separated by a comma
lockedResult = True
Case "Test", vbNullString ' All of these cases are OR as they are separated by a comma
lockedResult = False
Case Else
lockedResult = False
End Select
targetRow.EntireRow.locked = lockedResult
Next targetRow
targetSheet.Protect Password:=sheetPassword
End Sub

让我知道它是否有效

Answer 2

Sub test()
Dim cl As Object, strCells As String, lastRow As Long
With Sheets("Sheet1")
.Unprotect password:="Test" 
lastRow = .Cells(Rows.Count, 2).End(xlUp).Row 
strCells = "B1:B" & lastRow 
For Each cl In Range(strCells)
Select Case cl.Value
Case "TOP LEVEL", "No", "Maybe so" 
cl.EntireRow.Select
Selection.Locked = True
Case "Test" 
cl.EntireRow.Select
Selection.Locked = False 
Case Else
End Select
Next cl
.Protect 'password:="Test"
End With
End Sub

我添加EntireRow.Select然后下一行"选择.锁定=真/假。现在一切都很完美。