3x Checkboxes - Hide/Unhide sheet

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
Hi there,

I have 3x Form Check Boxes. If any of them are ticked, I would like sheet "Dairy" to be visible. If none of them are ticked, sheet "Dairy" should be hidden.

I have linked the 3x check box references to cells P1, P2 and P3. In P4 I have the following formula =IF(OR(P1,P2,P3=TRUE),TRUE,FALSE)

I then tried entering this VBA code under my current worksheet. However, it's not working. Is there anyway to get this working? I would like to avoid using an ActiveX Check Box if possible. Thanks!

Code:
Private Sub Worksheet_Calculate()
If [P4] = True Then
Sheets("Dairy").Visible = False
Else
Sheets("Dairy").Visible = True
End Sub
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,599
Your formula states, if P1 is checked, and P2 is checked, and P3 is checked, then True which means all 3 must be checked to unhide the sheet. But you want if any are checked, meaning even if only one is checked.
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
The OR part of the formula deals with that. If any of the 3x checkboxes are ticked, then P4 returns TRUE. If they are all unticked, P4 returns FALSE.
This formula works as I would expect.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
Use this

Code:
Private Sub Worksheet_Calculate()
    Sheets("Dairy").Visible = False
    If [P1] Or [P2] Or [P3] Then Sheets("Dairy").Visible = True
End Sub

Or Assign the following macro to the 3 checkboxes

Code:
Sub Hide_Unhide()
    Sheets("Dairy").Visible = False
    If [P1] Or [P2] Or [P3] Then Sheets("Dairy").Visible = True
End Sub
 
Last edited:

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
Once again, thank you Dante!
Assigning macro's to the 3x checkboxes did the trick :)
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
Hi Dante. This macro assigned to the check boxes was working perfectly, until I had to protect the worksheets.
I tried amending the code to include ActiveSheet.Unprotect ("") and ActiveSheet.Protect ("") but it just isn't working. I've tried a number of combinations too.
It works if my sheet G2 (where the macro is located) is unprotected and the other sheets protected. However, I need this to work with all the sheets protected.
Can you see where I might be going wrong and how I could fix it?
There are 6 macros/check boxes in total. Below is one of the straight forward ones..

Code:
Sub Beef()
    Sheets("Beef").Visible = False
    If [Q1] Then Sheets("Beef").Visible = True
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
Try this:

Code:
Sub Beef()
    Sheets("Beef").Unprotect
    Sheets("Beef").Visible = False
    If [Q1] Then Sheets("Beef").Visible = True
    Sheets("Beef").Protect
End Sub
I guess Q1 is on another sheet, right?
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
Cell Q1 is on Sheet G2.
Sheet G2 has all the options and macros on it. The other sheets (Beef for example) will appear if selected by the checkbox on sheet G2. If the Beef checkbox is selected, it returns TRUE in cell Q1, which in turn runs the macro and unhides the sheet "Beef".
With just the original coding (no Unprotect/Protect), and both sheets (G2 and Beef) are unprotected, it all works fine. If G2 is Unprotected and Beef Protected it also runs fine. If both sheets are protected, it returns an error that I'm trying to make a change on a protected sheet.
If I add your above code with both sheets protected, it returns the error that I am trying to change a protected sheet.

I have tried a number of combinations but none of them work when both sheets are protected.

These are what I have tried

Code:
Sub Beef()
    ActiveSheet.Unprotect ("")
    Sheets("Beef").Visible = False
    If [Q1] Then Sheets("Beef").Visible = True
    ActiveSheet.Protect ("")
End Sub
Code:
Sub Beef()
    Sheets("G2").Unprotect ("")
    Sheets("Beef").Visible = False
    If [Q1] Then Sheets("Beef").Visible = True
    Sheets("G2").Protect ("")
End Sub

Code:
Sub Beef()
    ActiveSheet.Unprotect ("")
    Sheets("Beef").Unprotect ("")
    Sheets("Beef").Visible = False
    If [Q1] Then Sheets("Beef").Visible = True
    ActiveSheet.Protect ("")
    Sheets("Beef").Protect ("")
End Sub
Code:
Sub Beef()
    Sheets("G2").Unprotect ("")
    Sheets("Beef").Unprotect ("")
    Sheets("Beef").Visible = False
    If [Q1] Then Sheets("Beef").Visible = True
    Sheets("G2").Protect ("")
    Sheets("Beef").Protect ("")
End Sub
They all return the same error, that I am trying to change a protected sheet. I'm not quite sure where to go from here..
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
Did you try what I put in post #8 ?

Code:
Sub Beef()
    [COLOR=#0000ff][B]Sheets("Beef")[/B][/COLOR].Unprotect
    Sheets("Beef").Visible = False
    If [Q1] Then Sheets("Beef").Visible = True
    [B][COLOR=#0000ff]Sheets("Beef")[/COLOR][/B].Protect
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,101,759
Messages
5,482,704
Members
407,359
Latest member
aaawww

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top