Error with checkbox to unhide sheet

[ABabeNChrist]

I am using this code below within a userform to unhide selected sheets using checkboxes.

Code: Select all

Private Sub CommandButton1_Click()
    MsgBox "You may now begin working on your report"

    ActiveWorkbook.Protect Password:="", Structure:=False, Windows:=False

    Dim wssheet As Worksheet

    Application.ScreenUpdating = False

    If CheckBox1.Value = True Then
        Sheets("Sheet1").Visible = xlSheetVisible
        Sheets("Sheet1").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                     Password:="", UserInterfaceOnly:=True
    End If

    If CheckBox2.Value = True Then
        Sheets("Sheet2").Visible = xlSheetVisible
        Sheets("Sheet2").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                             Password:="", UserInterfaceOnly:=True
    End If

    If CheckBox3.Value = True Then
        Sheets("Sheet3").Visible = xlSheetVisible
        Sheets("Sheet3").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                    Password:="", UserInterfaceOnly:=True
    End If
    Application.ScreenUpdating = True

    Worksheets("_").Visible = xlSheetHidden

    For Each wssheet In ActiveWorkbook.Worksheets
        If wssheet.Visible = xlSheetVisible Then
            wssheet.Select
            Exit For
        End If
    Next wssheet
    ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True

    Unload Me
    Unload UserForm1

But I noticed is, if no selection is made I would then receive an error
I then added
On Error GoTo Canceled
at the begining of code and
Canceled:
just before Workbook.Protect
this seems to get rid of the error but closes userform. So I then was trying to achieve a message saying “No checkbox was selected” if no ckeckbox was selected and for the userform not to unload and on the flip side I wanted a message saying “You may now begin working on your report” if any or all checkboxes are selected.
So I then added this code right after Next wssheet

Code: Select all

    MsgBox ("You may now begin working on your report")
Canceled:
    If Worksheets("_").Visible = True Then
        MsgBox ("No checkbox was selected")
        Exit Sub
    End If

To look something like

Code: Select all

Private Sub CommandButton1_Click()

    ActiveWorkbook.Protect Password:="", Structure:=False, Windows:=False

    Dim wssheet As Worksheet
    On Error GoTo Canceled

    Application.ScreenUpdating = False

    If CheckBox1.Value = True Then
        Sheets("Sheet1").Visible = xlSheetVisible
        Sheets("Sheet1").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                 Password:="", UserInterfaceOnly:=True
    End If

    If CheckBox2.Value = True Then
        Sheets("Sheet2").Visible = xlSheetVisible
        Sheets("Sheet2").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                 Password:="", UserInterfaceOnly:=True
    End If

    If CheckBox3.Value = True Then
        Sheets("Sheet3").Visible = xlSheetVisible
        Sheets("Sheet3").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                 Password:="", UserInterfaceOnly:=True
    End If
    Application.ScreenUpdating = True

    Worksheets("_").Visible = xlSheetHidden

    For Each wssheet In ActiveWorkbook.Worksheets
        If wssheet.Visible = xlSheetVisible Then
            wssheet.Select
            Exit For
        End If
    Next wssheet

    MsgBox ("You may now begin working on your report")

Canceled:
    If Worksheets("_").Visible = True Then
        MsgBox ("No checkbox was selected")
        Exit Sub
    End If
    ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True

    Unload Me
    Unload UserForm1
End Sub

so is this the correct way of doing this

[HansV]

I'd use a Boolean variable to keep track of whether a check box was ticked:

Code: Select all

Private Sub CommandButton1_Click()
    Dim wsSheet As Worksheet
    Dim blnSelected As Boolean

    Application.ScreenUpdating = False

    ActiveWorkbook.Protect Windows:=False

    If CheckBox1.Value = True Then
        blnSelected = True
        Sheets("Sheet1").Visible = xlSheetVisible
        Sheets("Sheet1").Protect DrawingObjects:=False, _
            UserInterfaceOnly:=True
    End If

    If CheckBox2.Value = True Then
        blnSelected = True
        Sheets("Sheet2").Visible = xlSheetVisible
        Sheets("Sheet2").Protect DrawingObjects:=False, _
            UserInterfaceOnly:=True
    End If

    If CheckBox3.Value = True Then
        blnSelected = True
        Sheets("Sheet3").Visible = xlSheetVisible
        Sheets("Sheet3").Protect DrawingObjects:=False, _
            UserInterfaceOnly:=True
    End If
    Application.ScreenUpdating = True

    If blnSelected = True Then
        Worksheets("_").Visible = xlSheetHidden
    End If

    For Each wsSheet In ActiveWorkbook.Worksheets
        If wsSheet.Visible = xlSheetVisible Then
            wsSheet.Select
            Exit For
        End If
    Next wsSheet

    ActiveWorkbook.Protect Windows:=True

    If blnSelected = True Then
        MsgBox "You may now begin working on your report"
        Unload Me
        Unload UserForm1
    Else
        MsgBox "You didn't tick any check box"
    End If
End Sub

[ABabeNChrist]

Thank you Hans
I made the changes as you requested and of course it worked great. I have a question. Was the method that I using incorrect, it did seem to work and why is using Boolean variable a better approach.
Just curious

[HansV]

Your method is correct too, but it relies on failure to hide the "_" sheet if no check boxes are ticked.
The method I proposed is more general: it actually checks whether at least one check box has been ticked.

[ABabeNChrist]

Very cool Thank you Hans