如何在受保护的 VBA 创建的工作表上解锁单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5192187/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to have unlocked cells on a protected VBA created worksheet
提问by david moore
An input file of data is processed using VBA to create an Excel(2003) protected spreadsheet(Invoice). The spreadsheet is then to be distributed to other offices where some designated cells are to be amended. How can I create the worksheet to allow these cells to be amended when the whole sheet is protected? I have tried using the code below, and other similar variations, but it does not seem to work. Can you help?
使用 VBA 处理数据的输入文件以创建 Excel（2003）保护的电子表格（发票）。然后将电子表格分发到要修改某些指定单元格的其他办公室。当整个工作表受到保护时，如何创建工作表以允许修改这些单元格？我曾尝试使用下面的代码和其他类似的变体，但它似乎不起作用。你能帮我吗？
Private Sub CellLock1()
Cells.Select
' unlock all the cells
Selection.Locked = False
' lock only these cells
Range("J49:K49").Select
Selection.Locked = True
ActiveSheet.Protect DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub
回答by Kovags
Every cell on excel is Locked by default and after protecting a workbook, you won't be able to edit the cells unless you unlock them beforehand.
默认情况下，excel 上的每个单元格都是锁定的，在保护工作簿后，除非事先解锁，否则您将无法编辑这些单元格。
You aren't able to unlock the cells, even using VBA code, if the sheet is protected. So if you want to use code to unlock some cells, you have to unprotect the workbook/worksheet first.
如果工作表受到保护，即使使用 VBA 代码，您也无法解锁单元格。因此，如果您想使用代码来解锁某些单元格，则必须先取消对工作簿/工作表的保护。
Please try my code:
请试试我的代码：
Sub UnlockCells()
Sheet1.Unprotect
Sheet1.Range("A1", "B6").Locked = False 'Unlock the range A1 to B6
Sheet1.Cells(6, 6).Locked = False 'Unlock the cell F6
Sheet1.Protect
End Sub
回答by Vikram
This may be a bit late ...but I hope it helps here are the steps to do:
这可能有点晚了......但我希望它有助于以下步骤：
- Lock the sheet under consideration
- View Code to create a private Subroutine(Right Click Sheet --> View Code --> Select the 'Microsoft Excel Objects' corresponding to this Sheet)
Paste this code :
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim ws As Worksheet Dim inputRange As Range Set ws = Worksheets("WorkSheetName") 'tell this sub to unprotect only these cells Set inputRange = Range("I5,I7,I11") ' If the selected cell is not in the range keep the sheet locked If Intersect(Target, inputRange) Is Nothing Then 'else unprotect the sheet by providing password '(same as the one that was used to protect this sheet) Else ws.Unprotect Password:="password" Target.Locked = False ws.Protect Password:="password" End If End Sub
- 锁定正在考虑的工作表
- 查看代码以创建私有子程序（右键单击工作表--> 查看代码--> 选择与此工作表对应的“Microsoft Excel 对象”）
粘贴此代码：
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim ws As Worksheet Dim inputRange As Range Set ws = Worksheets("WorkSheetName") 'tell this sub to unprotect only these cells Set inputRange = Range("I5,I7,I11") ' If the selected cell is not in the range keep the sheet locked If Intersect(Target, inputRange) Is Nothing Then 'else unprotect the sheet by providing password '(same as the one that was used to protect this sheet) Else ws.Unprotect Password:="password" Target.Locked = False ws.Protect Password:="password" End If End Sub
