News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Hide/unhide a tab based on a cell?

    Posted on WSalan sh Comment on the AskWoody Lounge

    Home Forums AskWoody support Productivity software by function MS Excel and spreadsheet help Hide/unhide a tab based on a cell?

    This topic contains 18 replies, has 8 voices, and was last updated by  WSnodell91 2 years, 7 months ago.

    • Author
      Posts
    • #481595 Reply

      WSalan sh
      AskWoody Lounger

      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

    • #1319832 Reply

      zeddy
      AskWoody_MVP

      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

      • #1319856 Reply

        krweaver
        AskWoody Lounger

        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.

        • #1319898 Reply

          zeddy
          AskWoody_MVP

          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

      • #1560993 Reply

        WSNitinV
        AskWoody Lounger

        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

        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

        • #1561105 Reply

          zeddy
          AskWoody_MVP

          Hi Nitin

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

          zeddy

      • #1592442 Reply

        WSbenlindell
        AskWoody Lounger

        Alan,

        I’m been trying to get your code to work in a Workbook of mine but can’t seem to get it functioning. I’ve attached the file, and if you can see anything wrong with how I have this set up, please let me know.

        Thanks!

        Ben46847-HO-Attributes-worksheet-w-ship-to-address-3.1.17

        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

        Attachments:
        • #1592512 Reply

          zeddy
          AskWoody_MVP

          Hi Ben

          Here’s an updated file that does what you want.

          NOTES:
          You cannot use the worksheet change event to do what you want using the radio-option-buttons.
          When a User selects an option button choice, it is Excel that changes the associated cell-link, not the User. The worksheet change event only triggers when a User makes a change to any cell(s) (e.g. by entering a value, editing a value, copying a cell and pasting it etc etc etc).
          It’s a bit like a cell with a formula: the formula-cell may change as a result of other cells being updated by the User, but it is Excel that ‘makes the changes’ to cells containg formulas (unless, of coure, the User edits the cell formula, or deletes it, which counts as a change.

          The other thing is that you must use either the worksheet tab name, or the vba codename for a worksheet, when you are referring to sheets in vba code.
          So your references to Sheets(“Sheet1”) should be Sheets(“Account info”) etc etc, since there is no tabname “Sheet1”

          So, to solve the issue, I just create this routine

          Code:
          Sub showOrHideMergeSheet()
          
          If Sheets("Account info").[a2] = 2 Then
          Sheets("Accounts to Merge").Visible = True
          Else
          Sheets("Accounts to Merge").Visible = False
          End If
          
          End Sub
          

          ..and assigned this routine to both the option-button shapes (i.e. right-click on them, then choose Assign macro..

          zeddy

          Attachments:
          • #1592516 Reply

            WSbenlindell
            AskWoody Lounger

            Thank you so much! This was my first time trying to figure out this functionality, so all your notes are extremely helpful. I really appreciate it!

            • #1592518 Reply

              zeddy
              AskWoody_MVP

              Hi Ben

              My apologies!
              It was your first post, I should’ve said “Welcome to the Lounge as a new Poster!”
              Thank you for letting us know it worked out OK.

              zeddy

        • #1593874 Reply

          WSnodell91
          AskWoody Lounger

          Hello Zeddy!

          I’m currently trying to use this for an entire column. We have drop down menus in one column, so based on the answer in that column, it will determine which of the 4 additional tab shows. I can’t seem to get it to work (coding is not my forte and I’m sure I’m doing everything wrong) and am wondering if you can see right away what is incorrect? The main thing that is standing out to me is where to tell the code which column I’m referring to… For example all of these answers are based on Column U, but how would the code know this without me saying what Target.Column equals?

          Private Sub Worksheet_Change(ByVal Target As Range)
          If Target.Column = “Lip” Then
          Sheets(“Cosmetics”).Visible = True
          Else
          Sheets(“Skincare”).Visible = False
          Sheets(“Devices”).Visible = False
          Sheets(“Supplements”).Visible = False
          End If
          If Target.Column = “Eye” Then
          Sheets(“Cosmetics”).Visible = True
          Else
          Sheets(“Skincare”).Visible = False
          Sheets(“Devices”).Visible = False
          Sheets(“Supplements”).Visible = False
          End If
          If Target.Column = “Foundation” Then
          Sheets(“Cosmetics”).Visible = True
          Else
          Sheets(“Skincare”).Visible = False
          Sheets(“Devices”).Visible = False
          Sheets(“Supplements”).Visible = False
          End If
          If Target.Column = “Face” Then
          Sheets(“Cosmetics”).Visible = True
          Else
          Sheets(“Skincare”).Visible = False
          Sheets(“Devices”).Visible = False
          Sheets(“Supplements”).Visible = False
          End If
          If Target.Column = “Brow” Then
          Sheets(“Cosmetics”).Visible = True
          Else
          Sheets(“Skincare”).Visible = False
          Sheets(“Devices”).Visible = False
          Sheets(“Supplements”).Visible = False
          End If
          If Target.Column = “Brushes_Sponges” Then
          Sheets(“Cosmetics”).Visible = True
          Else
          Sheets(“Skincare”).Visible = False
          Sheets(“Devices”).Visible = False
          Sheets(“Supplements”).Visible = False
          End If
          If Target.Column = “Nail” Then
          Sheets(“Cosmetics”).Visible = True
          Else
          Sheets(“Skincare”).Visible = False
          Sheets(“Devices”).Visible = False
          Sheets(“Supplements”).Visible = False
          End If
          If Target.Column = “Color_Multi_Sets” Then
          Sheets(“Cosmetics”).Visible = True
          Else
          Sheets(“Skincare”).Visible = False
          Sheets(“Devices”).Visible = False
          Sheets(“Supplements”).Visible = False
          End If
          If Target.Column = “Lash” Then
          Sheets(“Cosmetics”).Visible = True
          Else
          Sheets(“Skincare”).Visible = False
          Sheets(“Devices”).Visible = False
          Sheets(“Supplements”).Visible = False
          End If
          If Target.Column = “Cosmetic_Storage” Then
          Sheets(“Cosmetics”).Visible = True
          Else
          Sheets(“Skincare”).Visible = False
          Sheets(“Devices”).Visible = False
          Sheets(“Supplements”).Visible = False
          End If
          End Sub

          I apologize for my lack of understanding in this. Hoping you can help!

          Thanks!

    • #1319848 Reply

      WSalan sh
      AskWoody Lounger

      That will do nicely. Thanks.

      Alan

    • #1319861 Reply

      WSalan sh
      AskWoody Lounger

      Thats fine for me. If it’s not a “Yes”, then leave it hidden

      Alan

    • #1319965 Reply

      krweaver
      AskWoody Lounger

      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).

    • #1319983 Reply

      WSsdckapr
      AskWoody Lounger

      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

    • #1320005 Reply

      krweaver
      AskWoody Lounger

      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.

      • #1320076 Reply

        zeddy
        AskWoody_MVP

        Hi kweaver

        Then something is not quite right.
        If you put Yes i[/B]n 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

    • #1320117 Reply

      krweaver
      AskWoody Lounger

      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.

    • #1561111 Reply

      Maudibe
      AskWoody_MVP

      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
      

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Hide/unhide a tab based on a cell?

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.