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

 

零、背景：锁定EXCEL表头（不允许编辑部分单元格）

 

一、手动操作流程

其基本逻辑并不复杂，手动操作流程是：

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

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

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

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

 

二、VBA的方法

Function ProtectRange(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

    '锁定表(除了表头不允许修改，其他可以操作)   
    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 = 1
End Function

'调用方法：
'比如锁定 A1:F1的内容
Sub test()
    ProtectRange(“A1:F1”)
End Sub

 

三、疑难杂症

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

先要说明白3个逻辑：

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

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

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

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

 

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

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

 

四、解决方案

在worksheet_change 事件中，编写一段先解密再加密的程序即可(这样既保证了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

[5] https://zhidao.baidu.com/question/598163828.html

[6] http://club.excelhome.net/thread-1543118-1-1.html