Excel VBA 中的错误 1004 - 无法设置工作表类的可见属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5483638/
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
Error-1004 in Excel VBA- Unable to set the visible property of the worksheet class
提问by loknath
With the help of Excel forum, I have created a user login form where I have 5 users. Each user can have access to the sheets assigned to him/her only. This is working fine. But now I have protected the "workbook structure" so as to avoid users' adding/deleting sheets. Then I login again, and instead of displaying the login form, the error message pops up in Excel VBA:
在 Excel 论坛的帮助下，我创建了一个用户登录表单，其中有 5 个用户。每个用户只能访问分配给他/她的工作表。这工作正常。但是现在我已经保护了“工作簿结构”以避免用户添加/删除工作表。然后我再次登录，并没有显示登录表单，而是在 Excel VBA 中弹出错误消息：
Error-1004 Unable to set the visible property of the worksheet class
错误 1004 无法设置工作表类的可见属性
When I debug the error is highlighted in the following codes where the visible property of the worksheet is set as "True", "False" or "xlSheetVeryHidden".
当我调试时，错误在以下代码中突出显示，其中工作表的可见属性设置为“True”、“False”或“xlSheetVeryHidden”。
Private Sub Workbook_Open()
Dim wsSht As Worksheet
Worksheets("Splash").Visible = True
Worksheets("Users").Visible = False
For Each wsSht In Worksheets
If Not wsSht.Name = "Splash" Then wsSht.Visible = xlSheetVeryHidden
Next wsSht
With Worksheets("Splash")
.Visible = True
.Activate
End With
frmLogin.Show
bBkIsClose = False
End Sub
Is there a way to correct this so as I can access the login form as I did prior to password protecting the "workbook structure"?
有没有办法纠正这个问题，以便我可以像在密码保护“工作簿结构”之前那样访问登录表单？
回答by GoldBishop
Here is another concern about this.
这是另一个问题。
You can NOThide ALLof the worksheets in a workbook. As such if you know you are going to have at least 1 sheet that will ALWAYS be visible, exclude it from the hiding process.
您不能隐藏工作簿中的所有工作表。因此，如果您知道将至少有 1 张始终可见的工作表，请将其从隐藏过程中排除。
回答by Tiago Cardoso
Did you have another Excel Workbook opened at the same time when testing it? There's no explicit reference to the book you're looking for, so in case you run this code having a workbook where the "Splash" sheet is not available, the macro will try to set all sheets to hidden, which may raise this error.
测试时是否同时打开了另一个 Excel 工作簿？没有对您要查找的书的明确引用，因此如果您运行此代码的工作簿的“Splash”工作表不可用，则宏将尝试将所有工作表设置为隐藏，这可能会引发此错误。
To simulate it, open a new Excel session and run this macro:
要模拟它，请打开一个新的 Excel 会话并运行此宏：
Sub test()
Dim oSheet As Excel.Worksheet
For Each oSheet In Worksheets
oSheet.Visible = xlSheetVeryHidden
Next oSheet
End Sub
If I'm not barking to the wrong tree, you'll get the same error.
如果我没有向错误的树吠叫，你会得到同样的错误。
To solve it, simply add the workbook name into your loop, and it would be like this (obviously, you must ensure that there's a "Splash" sheet, or the error will arise):
要解决它，只需将工作簿名称添加到您的循环中，它会是这样的（显然，您必须确保有一个“Splash”表，否则会出现错误）：
For Each wsSht In Workbooks("Mybook.xlsm").Worksheets
If Not wsSht.Name = "Splash" Then wsSht.Visible = xlSheetVeryHidden
Next wsSht
Rgds
Rgds
回答by Dick Kusleika
You'll have to unprotect and reprotect in code. The downside is that your password will be in the code.
您必须在代码中取消保护和重新保护。缺点是您的密码将在代码中。
Private Sub Workbook_Open()
Dim wsSht As Worksheet
Me.Unprotect "password"
Me.Worksheets("Splash").Visible = True
Me.Worksheets("Users").Visible = False
For Each wsSht In Me.Worksheets
If Not wsSht.Name = "Splash" Then wsSht.Visible = xlSheetVeryHidden
Next wsSht
With Me.Worksheets("Splash")
.Visible = True
.Activate
End With
frmLogin.Show
bBkIsClose = False
Me.Protect "password", True, False
End Sub
