Announcement

Collapse
No announcement yet.

Hide/Unhide Sheets Based On Cell Value

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Hide/Unhide Sheets Based On Cell Value



    I have a series of sheets hidden in a workbook. (example sheets named a, b and c).

    I want to be able to unhide a sheet based on a Cell "A1" entry on sheet D

    So if A is entered, sheet A would unhide, and B and C would remain hidden.
    Last edited by gmccreedy; October 26th, 2006, 01:58.

  • #2
    Re: Unhide Sheet Based On Cell Value Contents

    You could use this, it will unhide the shet specified In A1 and hide the others. Place the code in worksheet D.

    Code:
    Private Sub worksheet_change(ByVal target As Excel.Range)
        Select Case Worksheets("D").Range("A1").Value
            Case "A"
                Worksheets("A").Visible = True
                Worksheets("B").Visible = False
                Worksheets("C").Visible = False
            Case "B"
                Worksheets("B").Visible = True
                Worksheets("A").Visible = False
                Worksheets("C").Visible = False
            Case "C"
                Worksheets("C").Visible = True
                Worksheets("A").Visible = False
                Worksheets("B").Visible = False
        End Select
    End Sub
    Attached Files
    Last edited by bryce; October 26th, 2006, 02:15.

    Comment


    • #3
      Re: Unhide Sheet Based On Cell Value Contents

      ok...my example was a little generic, I apologize. Is there a way to make this a loop as opposed to a series of "Case" statements?

      I actually have about 30 sheets.
      Last edited by gmccreedy; October 26th, 2006, 02:17.

      Comment


      • #4
        Re: Unhide Sheet Based On Cell Value Contents

        Try this one then.

        Code:
        Private Sub worksheet_change(ByVal target As Excel.Range)
            Dim ws As Worksheet
            
            For Each ws In Worksheets
                If ws.Name <> "D" And ws.Name <> Worksheets("D").Range("A1").Value Then
                    ws.Visible = False
                End If
                If ws.Name = Worksheets("D").Range("A1").Value Then
                    ws.Visible = True
                End If
            Next ws
                
        End Sub
        Attached Files

        Comment


        • #5
          Re: Unhide Sheet Based On Cell Value Contents

          Thank you so much!!! I was so close...had small typo in the If statement...thanks again!!!

          Comment


          • #6
            Re: Hide/Unhide Sheets Based On Cell Value

            How would you convert the 'case' code if A1 had a formula (vlookup) to return "A", "B" or "C"?

            Comment


            • #7
              Re: Unhide Sheet Based On Cell Value Contents

              Hi! This is awesome, almost exactly what I'm was trying to do but I'm trying to be cable to make this work with a column of vaults like having column A in sheet D determine if the sheets are visible then have it go like if A1=yes then Sheet A is visible, If A2=yes then Sheet B is visible, so on and so forth.Or maybe even have the yes no part as a drop down menu. I'm still trying to learn this programming stuff so if you could help me and explain how the code works that would be much appreciated.
              Last edited by aznblood5; August 7th, 2014, 06:42. Reason: New thought

              Comment


              • #8


                Re: Unhide Sheet Based On Cell Value Contents

                Bryce,

                Your hide code works perfectly for what I needed, I made a minor change so it is a YES/NO question basically and it worked well.

                I want to add a second set of logic applying to anther cell how do I do put this into the VBA. I tried adding a second copy of the code with a different Range Cell but it didn't work after I did that.

                Sorry for the rookie question I have zero VBA experience. likely I made a foolish error.

                Cheers
                Kent




                Private Sub worksheet_change(ByVal target As Excel.Range)
                Select Case Worksheets("D").Range("A1").Value
                Case "YES"
                Worksheets("A").Visible = True
                Worksheets("B").Visible = False
                Worksheets("C").Visible = False
                Case ""
                Worksheets("B").Visible = False
                Worksheets("A").Visible = False
                Worksheets("C").Visible = False

                End Select
                End Sub

                Private Sub worksheet_change(ByVal target As Excel.Range)
                Select Case Worksheets("D").Range("B1").Value
                Case "YES"
                Worksheets("A").Visible = False
                Worksheets("B").Visible = True
                Worksheets("C").Visible = False
                Case ""
                Worksheets("B").Visible = False
                Worksheets("A").Visible = False
                Worksheets("C").Visible = False

                End Select
                End Sub

                Comment

                Working...
                X