Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    Hide/unhide a tab based on a cell?

    Is there an easy way for a tab to change from hidden to unhidden (and vice versa) depending on the value of a cell (in another tab, of course).

    Example - in tab 1, I have a question with a Yes/No pulldown. If the user selects "Yes", then another tab is unhidden. If they select "No", it gets hidden again.

    Thanks

    Alan

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Alan

    Yes, you could use the Worksheet_Change event:
    Right-click on the tab 1 sheet, select View Code, add the following and change the cell [A7] to the correct cell address you are using.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If [a7] = "Yes" Then
    Sheets("Sheet3").Visible = True
    Else
    Sheets("Sheet3").Visible = False
    End If
    End Sub


    zeddy

  3. #3
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    That will do nicely. Thanks.

    Alan

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Interesting! However, I noticed that changing the cell value to "no" or "false" or something else, the sheet remains hidden.
    (Excel 2010)

    Curious if there's a toggle to this.
    Last edited by kweaver; 2012-02-16 at 10:46.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Thats fine for me. If it's not a "Yes", then leave it hidden

    Alan

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi kweaver

    I intended the sheet only to be visible if the cell contained "Yes" otherwise the sheet would be hidden.
    But you could have pointed out that if the cell contained "yes" or, even worse, "Yes " (with a space) then it would also remain hidden (because I was being very very specific in the code i.e. If [a7] = "Yes" Then..
    However, since the cell entry was supposed to be selected via dropdown I didn't bother with other proper tests.

    zeddy

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    I figured as much (re your intention). What I'm curious about since this grabbed my attention is if there's a way to toggle the appearance of a sheet based on a cell entry. If Yes, have the sheet appear; if No have the sheet not appear (or something like that).

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    if there's a way to toggle the appearance of a sheet based on a cell entry. If Yes, have the sheet appear; if No have the sheet not appear (or something like that).
    How is that different from the code already posted?

    Steve

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    When I put in the code, changed the cell, the sheet was hidden. When I changed it back or to something else, the sheet did not unhide.

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi kweaver

    Then something is not quite right.
    If you put Yes in the relevant cell, the specified sheet will unhide.
    If it doesn't then check that you are putting it in the right cell, that it is definitely Yes and not something else.
    The only other explanation is that you have Events turned Off somehow.
    Please let us know! We want to fix this for you!

    zeddy

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Very ODD...I just opened a new/fresh workbook and recopied the macro and it does work as advertised. Wonder what caused that earlier problem. Hmmm. Head scratch.

  12. #12
    New Lounger
    Join Date
    Apr 2016
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need additional Help

    I have used this VBA code, however If I have multiple (say 25) sheets how can I add more variables? I am new to VBA so any help is appreciated.

    In my case I have used following VBA code, Now I have 24 more sheets lined up with drop down list in next rows (B3 ,B4,....)

    Can you show me how to add more variables?

    Regards,
    Nitin

    Quote Originally Posted by zeddy View Post
    Hi Alan

    Yes, you could use the Worksheet_Change event:
    Right-click on the tab 1 sheet, select View Code, add the following and change the cell [A7] to the correct cell address you are using.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If [b2] = "Yes" Then
    Sheets("Sheet3").Visible = True
    Else
    Sheets("Sheet3").Visible = False
    End If
    End Sub


    zeddy

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Nitin

    Tell us a little more of what you want to do.
    Then we can show you how.

    zeddy

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Nitin,

    The following code placed in sheet1 module will toggle the other sheets visibility by answering "Yes"/anything else in column B. Following your example, B2 toggles sheet 3, B3 toggles sheet 4, B4 toggles Sheet 5, etc.

    "Yes" is not case sensitive and the code is generic so that it does not matter what the sheet is named.

    HTH,
    Maud

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("B2:B26")) Is Nothing Then
        Row = Target.Row
        If LCase(Target) = "yes" Then
            Worksheets(Row + 1).Visible = True
        Else
            Worksheets(Row + 1).Visible = False
        End If
    End If
    End Sub
    Last edited by Maudibe; 2016-04-22 at 14:20.

Posting Permissions

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