Skip to main content

锁定单元格

需求

  1. 锁定所有单元格
  2. 如果单元格为空,可以进行编辑
  3. 如果单元格不为空,对其进行锁定

处理

锁定单元格

Function ProtectByRangeStr(rng As Variant)
Application.ScreenUpdating = False
Dim sh, rg, cell
Set sh = ActiveSheet

'解锁表
sh.Unprotect "123456"

'选择所有的单元格并设置不锁定
sh.Cells.Locked = False

'选择目标单元格,并设置锁定
'sh.Range(rng).Locked = True

'如果单元格不为空,就锁定
For Each c In sh.Range(rng).Cells
If Not IsEmpty(c) Then c.Locked = True
Next

'锁定表(除了表头不允许修改,其他可以操作)
sh.Protect Password:="123456", _
UserInterFaceOnly:=True, _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Application.ScreenUpdating = True
End Function


'监听修改事件
Private Sub Worksheet_Change(ByVal Target As Range)
lockSheet
End Sub


'调用方法:
'比如锁定 A1:F1的内容
Sub lockSheet()
ProtectByRangeStr ("B3:G7")
End Sub

参考