Hide/Unhide Sheets based on Multiple ActiveX Checkbox selections

[TerryF]

I am attempting to build a workbook with approximately 30 sheets. When the user opens the workbook, there will be a set-up sheet with two questions. Based on the selections, the workbook will begin to unhide/hide the relevant sheets keeping the others hidden.

Example:
1) What Products are you supporting?
"Box" Chainsaw
"Box" Lawnmower
"Box" Cement Mixer

2) Will you be Renting or providing the Labor?
"Box" Rental
"Box" Labor

I have figured out how to make the sheets visible based on a single ActiveX Checkbox selection. However, I need to somehow unhide and hide sheets based on both of the question selections in a dynamic way. Some sheets are common to each of the products and others are independent.

Question (1) can be multiple selections where question (2) will be one or the other.

Any help will be appreciated. Thanks in advance.

 

[JustynaMK]

Hi TerryF,

Happy New Year.

If for your each answer you use a separate CheckBox, you can use the following macro:

Sub HideUnhide()


    With Sheet1
        If .CheckBox1.Value = True And .CheckBox2.Value = True Then
            'Both are checked - unhide some sheets
        Else
            'At least one of them is not checked - hide some sheets
        End If
    End With
End Sub

Or even easier:

If .CheckBox1.Value And .CheckBox2.Value Then

"True" Value indicates that a given CheckBox has been checked.
Let me know if that helps.

 

[TerryF]

Thank you JustynaMK. I was able to get that code to work. However, is there a way to place multiple With or If commands within one macro/

CheckBox1 & CheckBox2 are working within the the "With - End With". Can I also somehow code in CheckBox2 & Checkbox3 within that same macro? This way I can have the user select questions 1 & 2 then click a "Create Workbook" command button to run the Sub.

 

[JustynaMK]

Hi Terry,

I think simply using additional ElseIf statement(s) will work for you, e.g.:

        If .CheckBox1.Value And .CheckBox2.Value Then
            'Both are checked - unhide some sheets
        ElseIf .CheckBox2.Value And .CheckBox3.Value Then
            'Both are checked - unhide some other sheets when 2 & 3 are checked
        ElseIf .CheckBox4.Value And .CheckBox5.Value Then
            'This is optional - you can have as many ElseIf statements as you want
        Else
            'At least one of them is not checked - hide some sheets
        End If

 

[TerryF]

Perfect.
Thank you for the help.

 

[JustynaMK]

You're welcome! Take care.