Protect Workbook Struture and Printing Hidden Worksheet

[Jenny_MO]

I'm having some trouble with the command button code below when I protect my workbook. I can protect the worksheet the button is on and lock it down so everything is tight, but as soon as I protect the workbook, I get a "Run-time error '1004': Method 'Visible' of object'_Worksheet' failed. Thank you!

Sub Print_Quote()
    Dim CurVIs As Long
    Dim ws As Worksheet
    For Each ws In Sheets(Array("Sheet Name"))
        With ws
            CruVis = .Visible
            If CurVIs >= 0 Then
                .Visible = xlSheetVisible
                .ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:="C:\File Name.pdf", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
                .Visible = xlSheetHidden
            End If
        End With
    Next ws
End Sub

 

[SpillerBD]

It looks like your code will make all the sheets hidden, or try to. there must be at least one visible sheet.

 

[Jenny_MO]

Thanks SpillerBD

There is a visible sheet - the calculation sheet. The print button code is pulling from a hidden sheet to "print" an adobe file that can be saved and/or printed. It works as expected until I choose to protect workbook, then it errors out. I think I need some kind of Unprotect/Protect script, but I don't know how to do that with an entire workbook.

Jenny