保护 Excel 中的单元格，但允许通过 VBA 脚本修改这些单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/125449/
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
Protecting cells in Excel but allow these to be modified by VBA script
提问by Dheer
I am using Excel where certain fields are allowed for user input and other cells are to be protected. I have used Tools Protect sheet, however after doing this I am not able to change the values in the VBA script. I need to restrict the sheet to stop user input, at the same time allow the VBA code to change the cell values based on certain computations.
我正在使用 Excel，其中允许用户输入某些字段并保护其他单元格。我已经使用了 Tools Protect 表，但是这样做之后我无法更改 VBA 脚本中的值。我需要限制工作表以停止用户输入，同时允许 VBA 代码根据某些计算更改单元格值。
回答by Joe
Try using
尝试使用
Worksheet.Protect "Password", UserInterfaceOnly := True
If the UserInterfaceOnly parameter is set to true, VBA code can modify protected cells.
如果 UserInterfaceOnly 参数设置为 true，则 VBA 代码可以修改受保护的单元格。
回答by Robert Mearns
You can modify a sheet via code by taking these actions
您可以通过执行这些操作来通过代码修改工作表
- Unprotect
- Modify
- Protect
- 取消保护
- 调整
- 保护
In code this would be:
在代码中，这将是：
Sub UnProtect_Modify_Protect()
ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="Password"
'Unprotect
ThisWorkbook.ActiveSheet.Range("A1").FormulaR1C1 = "Changed"
'Modify
ThisWorkbook.Worksheets("Sheet1").Protect Password:="Password"
'Protect
End Sub
The weaknessof this method is that if the code is interrupted and error handling does not capture it, the worksheet could be left in an unprotected state.
这种方法的弱点是如果代码被中断并且错误处理没有捕获它，工作表可能会处于不受保护的状态。
The code could be improvedby taking these actions
可以通过采取这些行动来改进代码
- Re-protect
- Modify
- 重新保护
- 调整
The code to do this would be:
执行此操作的代码是：
Sub Re-Protect_Modify()
ThisWorkbook.Worksheets("Sheet1").Protect Password:="Password", _
UserInterfaceOnly:=True
'Protect, even if already protected
ThisWorkbook.ActiveSheet.Range("A1").FormulaR1C1 = "Changed"
'Modify
End Sub
This code renews the protection on the worksheet, but with the ‘UserInterfaceOnly' set to true. This allows VBA code to modify the worksheet, while keeping the worksheet protected from user input via the UI, even if execution is interrupted.
此代码更新工作表上的保护，但将“UserInterfaceOnly”设置为 true。这允许 VBA 代码修改工作表，同时保护工作表不受用户通过 UI 输入的影响，即使执行被中断。
This setting is lostwhen the workbook is closed and re-opened. The worksheet protection is still maintained.
当工作簿关闭并重新打开时，此设置将丢失。工作表保护仍然保留。
So the 'Re-protection' code needs to be included at the start of any procedure that attempts to modify the worksheet or can just be run once when the workbook is opened.
因此，任何尝试修改工作表的过程开始时都需要包含“重新保护”代码，或者在打开工作簿时只能运行一次。
回答by Robert Mearns
A basic but simple to understand answer:
一个基本但简单易懂的答案：
Sub Example()
ActiveSheet.Unprotect
Program logic...
ActiveSheet.Protect
End Sub
回答by hegemon
As a workaround, you can create a hidden worksheet, which would hold the changed value. The cell on the visible, protected worksheet should display the value from the hidden worksheet using a simple formula.
作为一种解决方法，您可以创建一个隐藏的工作表，其中将保存更改后的值。可见的受保护工作表上的单元格应使用简单公式显示隐藏工作表中的值。
You will be able to change the displayed value through the hidden worksheet, while your users won't be able to edit it.
您将能够通过隐藏的工作表更改显示的值，而您的用户将无法对其进行编辑。
回答by Kevin Crumley
I don't think you can set any part of the sheet to be editable only by VBA, but you can do something that has basically the same effect -- you can unprotect the worksheet in VBA before you need to make changes:
我不认为您可以将工作表的任何部分设置为只能由 VBA 编辑，但是您可以做一些具有基本相同效果的事情——您可以在需要进行更改之前取消保护 VBA 中的工作表：
wksht.Unprotect()
and re-protect it after you're done:
完成后重新保护它：
wksht.Protect()
Edit: Looks like this workaround may have solved Dheer's immediate problem, but for anyone who comes across this question/answer later, I was wrong about the first part of my answer, as Joe points out below. You canprotect a sheet to be editable by VBA-only, but it appears the "UserInterfaceOnly" option can only be set when calling "Worksheet.Protect" in code.
编辑：看起来这个解决方法可能已经解决了 Dheer 的直接问题，但是对于以后遇到这个问题/答案的任何人来说，我的答案的第一部分是错误的，正如乔在下面指出的那样。您可以保护工作表只能由 VBA 编辑，但似乎只有在代码中调用“Worksheet.Protect”时才能设置“UserInterfaceOnly”选项。
回答by Alan
I selected the cells I wanted locked out in sheet1 and place the suggested code in the open_workbook() function and worked like a charm.
我选择了我想在 sheet1 中锁定的单元格，并将建议的代码放置在 open_workbook() 函数中，并且效果很好。
ThisWorkbook.Worksheets("Sheet1").Protect Password:="Password", _
UserInterfaceOnly:=True
