3x Checkboxes - Hide/Unhide sheet
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: 3x Checkboxes - Hide/Unhide sheet

  1. #1
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    260
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 3x Checkboxes - Hide/Unhide sheet

    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

  2. #2
    Board Regular
    Join Date
    May 2014
    Posts
    1,599
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 3x Checkboxes - Hide/Unhide sheet

    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.

  3. #3
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    260
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 3x Checkboxes - Hide/Unhide sheet

    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.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,322
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: 3x Checkboxes - Hide/Unhide sheet

    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 by DanteAmor; Jun 19th, 2019 at 11:07 PM.
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    260
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 3x Checkboxes - Hide/Unhide sheet

    Once again, thank you Dante!
    Assigning macro's to the 3x checkboxes did the trick

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,322
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: 3x Checkboxes - Hide/Unhide sheet

    Quote Originally Posted by tlc53 View Post
    Once again, thank you Dante!
    Assigning macro's to the 3x checkboxes did the trick
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    260
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 3x Checkboxes - Hide/Unhide sheet

    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

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,322
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: 3x Checkboxes - Hide/Unhide sheet

    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?
    Regards Dante Amor

  9. #9
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    260
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 3x Checkboxes - Hide/Unhide sheet

    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 by tlc53; Jun 24th, 2019 at 06:11 PM.

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,322
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: 3x Checkboxes - Hide/Unhide sheet

    Did you try what I put in post #8 ?

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •