如何在 Excel VBA 中保护具有不同选项的工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10397540/
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 do I protect sheets with different options in Excel VBA?
提问by user1187347
I am trying to protect sheets (visible only) in my Excel workbook in VBA. I want to allow users to edit objects on "Sheet 2" and "Sheet 3", but not on the others.
我正在尝试在 VBA 中保护 Excel 工作簿中的工作表（仅可见）。我想允许用户编辑“Sheet 2”和“Sheet 3”上的对象，但不能编辑其他对象。
This is the code I'm using.
这是我正在使用的代码。
Public Sub WBOpen()
Dim sh As Worksheet
Dim allowObjects As Boolean
For Each sh In Sheets
If sh.Visible = xlSheetVisible Then
**If sh.Name = "Sheet 2" Or "Sheet 3" Then**
allowObjects = True
Else
allowObjects = False
End If
sh.Protect Password:=pw(sh), DrawingObjects:=allowObjects, Contents:=True, Scenarios:=True, AllowFormattingRows:=True, AllowFiltering:=True, UserInterfaceOnly:=True
End If
Next
End Sub
I'm getting a type mismatch error on the line I've wrapped with **. Can anyone tell me where I'm going wrong?
我在用 ** 包裹的行上遇到类型不匹配错误。谁能告诉我我哪里出错了？
Thank you
谢谢
采纳答案by mwolfe02
That line should be:
该行应该是：
If sh.Name = "Sheet 2" Or sh.Name = "Sheet 3" Then
The type mismatch error comes because you are treating a string ("Sheet 3") as a boolean (True/False).
出现类型不匹配错误是因为您将字符串 ( "Sheet 3") 视为布尔值 ( True/ False)。
回答by Rob I
Not sure why it would still have an error after the error @Randy noted was fixed, but I can tell you what I do. Instead of iterating through every sheet, I access the sheet I want to protect directly. This is done by the codename, not the name in the GUI. For example, in the VBA editor, in the Project Explorer, under the "Microsoft Excel Objects" folder, I click on my "Testing" sheet and see that it has a "(Name)" property of "TestingSheet". Note that this is probably just an optional step, for clarity, as my other sheets have names too - like "Sheet1". Then in the code I just type:
不知道为什么在@Randy 指出的错误被修复后它仍然会出现错误，但我可以告诉你我做了什么。我没有遍历每个工作表，而是直接访问我想要保护的工作表。这是通过代码名称完成的，而不是 GUI 中的名称。例如，在 VBA 编辑器中，在项目资源管理器中，在“Microsoft Excel 对象”文件夹下，我单击“测试”表并看到它具有“TestingSheet”的“（名称）”属性。请注意，为了清楚起见，这可能只是一个可选步骤，因为我的其他工作表也有名称 - 例如“Sheet1”。然后在代码中我只输入：
TestingSheet.Protect , True, True, True, True
If you did this, you would avoid the runtime error, and you would also be more flexible in terms of the GUI name of the worksheets.
如果您这样做，您将避免运行时错误，并且您在工作表的 GUI 名称方面也会更加灵活。
