Error - Method ‘Protect’ of object’_Worksheet’ failed

[ABabeNChrist]

I am using this code below inside my Workbook module that will apply protection to all my sheets when workbook is opened. It seems that I am receiving an error message when workbook first opens up.

Code: Select all

Private Sub Workbook_Open()
    Dim wsh As Worksheet
    For Each wsh In Worksheets
        wsh.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                    Password:="", UserInterfaceOnly:=True
    Next wsh
End Sub

here is my error message

Error message.JPG

on this line

Code error.JPG

[HansV]

Please post a stripped down copy of the workbook.

[ABabeNChrist]

Please post a stripped down copy of the workbook.

Hi Hans
This workbook is very large, stripping it down may not be that simple. What I did notice was that this error only seems to occur when I first open up the workbook and that all the visible sheet that are listed at the bottom look to be a little faded as if not selected. When I select one of the sheets below and then re save all seems to be good. Is there a way I may prevent this from occurring again.

[HansV]

Without seeing the workbok, I have no way of knowing why the error occurs. You could suppress the error message by inserting a line

On Error Resume Next

at the beginning of Workbook_Open, but test it thoroughly to see if sheets remain unprotected.

[ABabeNChrist]

Hi Hans
I thought before trying to use
On Error Resume Next
I thought I might check things a little further. I believe I was able to narrow it down a little bit closer to the problem. It seems that when I have completed the report and I use this code to print

Code: Select all

Private Sub CommandButton2_Click()
    Dim wssheet As Worksheet
    Application.ScreenUpdating = False
    For Each wssheet In ActiveWorkbook.Worksheets
    
    Next wssheet
    
    If CheckBox1.Value And Sheets("Cover Page").Visible = xlSheetVisible Then _
       Sheets("Cover Page").Select Replace:=True
    If CheckBox2.Value And Sheets("Client Information").Visible = xlSheetVisible Then _
       Sheets("Client Information").Select Replace:=False
    If CheckBox3.Value And Sheets("Utilities").Visible = xlSheetVisible Then _
       Sheets("Utilities").Select Replace:=False

    ActiveWindow.SelectedSheets.PrintOut

    For Each wssheet In ActiveWorkbook.Worksheets
    Next wssheet
    
    Application.ScreenUpdating = True

End Sub

I would then close and save workbook. The problem then seems to occur.
The way I found it was I kept going through one piece of code at a time, then close and save, until I found the culprit.
As I mentioned before if select a sheet and close and save from there every things good.
So what I thought was, add some additional code to the print code that would select a sheet.
so I added

Code: Select all

    Unload Me
    Unload UserForm4
    Worksheets("Cover Page").Select
    ActiveSheet.Range("D7").Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                        Password:="", UserInterfaceOnly:=True
    ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True

right after
Application.ScreenUpdating = True
I'm not sure if this is the right approach or not but it seems to work

[HansV]

You can remove the declaration

Dim wssheet As Worksheet

and both instances of

For Each wssheet In ActiveWorkbook.Worksheets

Next wssheet

Since there isn't anything between the For and Next lines, the code doesn't do anything at all.

Instead of adding code to CommandButton2_Click, you could add a single line at the beginning of Workbook_Open:

Worksheets("Cover Page").Select

[ABabeNChrist]

Hi Hans
I made the changes and removed the useless pieces of code that was doing nothing.
The reason I wanted to add this piece of code that would select the Cover Page here at the end of the print section is because when the workbook is first opened the Cover Page is hidden.
I also wanted to ask is if I wanted to bring up a MsgBox if no checkbox is ticked. I tried using a Boolean variable to keep track of whether a check box was ticked. But this did not seem to work.
I used
Dim blnSelected As Boolean
then added to each check box

Code: Select all

    If CheckBox1.Value And Sheets("Cover Page").Visible = xlSheetVisible Then _
       Sheets("Cover Page").Select Replace:=True And blnSelected = True

and then at the end

Code: Select all

    If blnSelected = True Then
        MsgBox ("")
    Else
        MsgBox ("")

    End If

[HansV]

You cannot combine two commands on one line with And. And is used to test multiple conditions, as in your If ... Then line. Change the code to

Code: Select all

    If CheckBox1.Value And Sheets("Cover Page").Visible = xlSheetVisible Then
        Sheets("Cover Page").Select Replace:=True
        blnSelected = True
    End If

Note that there is no underscore after Then anymore.

[ABabeNChrist]

Thank you so very much Hans
I believe this may have solved my problem