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

Question

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 access the login form as I did prior to password protecting the "workbook structure"?

Answer 1

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.

Answer 2

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