疑难杂症 | Excel VBA锁定指定单元格区域

时间:2022-10-15 20:21:22

背景:锁定EXCEL表头

一、手动操作流程

其基本逻辑并不赋值,手动操作流程是:

1、取消所有单元格的“锁定”格式

CTRL+A,选中全部的单元格→单击右键→设置单元格格式→保护→取消勾选锁定

2、选中指定单元格(需要锁定的单元格)→单击右键→设置单元格格式→保护→勾选锁定

3、点击“审阅”→保护工作表→下面的复选框全部选择→确定→输入密码→再次输入密码即可

二、VBA的方法

 1 Function ProtectRange(rng As Variant)
2 Application.ScreenUpdating = False
3 Dim sh, rg, cell
4 Set sh = ActiveSheet
5
6 '解锁表
7 sh.Unprotect "123456"
8
9 '选择所有的单元格并设置不锁定
10 sh.Cells.Locked = False
11
12 '选择目标单元格,并设置锁定
13 sh.Range(rng).Locked = True
14
15 '锁定表(除了表头不允许修改,其他可以操作)
16 sh.Protect Password:="123456", _
17 UserInterFaceOnly:=True, _
18 DrawingObjects:=False, _
19 Contents:=True, _
20 Scenarios:=False, _
21 AllowFormattingCells:=True, _
22 AllowFormattingColumns:=True, _
23 AllowFormattingRows:=True, _
24 AllowInsertingColumns:= True, _
25 AllowInsertingRows:=True, _
26 AllowInsertingHyperlinks:=True, _
27 AllowDeletingColumns:=True, _
28 AllowDeletingRows:=True, _
29 AllowSorting:=True, _
30 AllowFiltering:=True, _
31 AllowUsingPivotTables:=True
32 Application.ScreenUpdating = 1
33 End Function
34
35 '调用方法:
36 '比如锁定 A1:F1的内容
37 Sub test()
38 ProtectRange(“A1:F1”)
39 End Sub

三、疑难杂症

以上代码本身没有问题,但是我在做某一行(非锁定行)的删除操作的时候,系统提示:“您正试图删除包含有锁定单元格的一行。锁定单元格在工作表受保护时无法删除”。

先要说明白3个逻辑:

(1)如果一行中任意一个单元格设置了“锁定”,在工作表保护时,即使设置了允许删除行,实际上也是删除不了的。假如能删除的话,就破坏了工作表保护这种机制,工作表保护、单元格保护就失去了它原有的作用。

(2)如果一行中所有单元格都取消勾选“锁定”,那么在工作表保护状态下勾选允许“删除行”时,就可以删除这一行。

(3)如果在工作表保护时,没有勾选允许“删除行”,即使所有单元格都取消勾选“锁定",也是不能删除这一行的。

但是我可以明确的是,我删除的行中所有单元格没有被锁定,且已经设置了允许删除行。比如我设置的是第一行锁定,其他单元格统统未被锁定,理论上除了第一行之外是可以删除的。但是系统始终提示:“您正试图删除包含有锁定单元格的一行。锁定单元格在工作表受保护时无法删除”。

经过吃饭的瞬间,我想问题想通了:原因在于,我在编写vba程序的时候调用了change事件,换句话说,整个change事件影响了系统做出判断,系统以为我正在对锁定的第一行进行操作,但是实际上是我在非第一行操作。因此系统进行了报错。

为了验证我的看法,我又测试了另外一个没有change事件的vba程序,的确可以正常删除。由此验证,change事件可能不能与锁定特定单元格的代码同步运行,内部可能存在矛盾机制。希望对有缘人有用!

四、参考资料

[1] http://www.360doc.com/content/17/0510/21/30583536_652812755.shtml

[2] https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.protect

[3] https://docs.microsoft.com/zh-cn/office/vba/api/excel.range.locked

[4] https://blog.csdn.net/rooklyn21/article/details/22298657