Error-1004 in Excel VBA- Unable to set the visible property of the worksheet class

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:

Error-1004 Unable to set the visible property of the worksheet class

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".

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 acc开发者_如何学Cess the login form as I did prior to password protecting the "workbook structure"?

Here is another concern about this.

You can NOT hide ALL of 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.

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.

To simulate it, open a new Excel session and run this macro:

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):

For Each wsSht In Workbooks("Mybook.xlsm").Worksheets
    If Not wsSht.Name = "Splash" Then wsSht.Visible = xlSheetVeryHidden
Next wsSht

Rgds

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

I'm not sure if this is relevant, but I found this question when I was searching for how to hide the last (and only) worksheet. The reason for wanting to do this is because the file is a startup file that contains company-wide macros that should not be edited by the user. I discovered that while the Worksheet needs to be kept open, the Window displaying it does not.

Here's an example:

Sub spork()
  Dim x As Workbook
  x.Windows.Item(1).Visible = False
End Sub

Now, Excel opens the file with no worksheet visible.

Sometimes there is a "glitch" (a.k.a. "feature" or "bug"), simply returning this error due to no visible reason. Not the worksheet protection, neither the fact that this is the last worksheet visible. To fix the "feature" this worked for me:

Public Sub UnhideAll()
    
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
        wks.Visible = xlSheetVisible
    Next
    
End Sub