Thread: Unable to set the visible property of the worksheet class

Hi All,

I've been using the following code successfully for years. Today I would get the error 1004:Unable to set the visible property of the worksheet class. All my searches came up with someone not realizing they had protected the workbook. As you can see, the first thing I do before trying to set the worksheet visible is to unprotect the workbook. In frustration and on a lark I tried .Sheets("items").Visible = True and it worked. These kind of intermittent errors in Excel VBA are very frustrating. Can anyone tell me what I am doing wrong?

Thanks.

Sub Unprotect()

Dim c As Integer
Dim wkbk As Workbook

Application.ScreenUpdating = False

    With Application.FileSearch
        .LookIn = ThisWorkbook.Path
        .FileType = msoFileTypeExcelWorkbooks
            If .Execute > 0 Then
                For c = 1 To .FoundFiles.Count
                    Set wkbk = Workbooks.Open(.FoundFiles(c))
                    With wkbk
                        .Unprotect "mypassword"
                        .Sheets("items").Visible = xlVisible
                        .Save
                        .Close
                    End With
                Next c
            Else
                MsgBox "No workbooks"
            End If
    End With

Application.ScreenUpdating = True
    
End Sub

Hello colinnwn,

I am not sure what your question is. I can tell you that the Excel constant xlVisible is equal to -1. The boolean value True also equals -1. So in this case, it doesn't matter which one use.

Sincerely,
Leith Ross

Originally Posted by shg

The property you want is Hidden, not Visible

The Worksheet class does not have a Hidden property, but does have a Visible one. The problem is just the constant, unless I'm going nuts (again).

Thanks romperstomper, that makes more sense. I knew the visible property worked previously. I don't remember changing the code to xlvisible vs. xlsheetvisible, but the fact xlsheetvisible enumerates to the same thing that ended up working for me (true), is rational.