|
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked And Sheets(1).ProtectContents And Target.Count = 1 Then
ps = InputBox("本单元格已被锁定,如修改或删除数据,请输入解锁密码")
If ps <> "123" Then
MsgBox "解锁密码错误,无权限修改"
Exit Sub
Else
Sheets(1).Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
End If
End If
End Sub
Private Sub Worksheet_Deactivate()
'一旦点击本表之外的其它表或关闭工作簿,则触发此事件,即将本表中数据全部锁定,并保护工作表
Sheets(1).Unprotect "123"
n = [A65536].End(3).Row: ar = Range("A2:A" & n)
For i = 1 To n - 1
s = ar(i, 1)
d1 = VBA.CDate(Year(s) & "-" & Month(s) & "-" & Day(s))
d2 = Date
If d1 = d2 Then
p = i
Exit For
End If
Next
Rows("1:" & 5000).Locked = False
Rows("1:" & p).Locked = True
Sheets(1).Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
End Sub |
|