Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Hide/Unhide Sheets Based On Cell Value

  1. #1
    Join Date
    25th April 2005
    Location
    Oakland,New Jersey
    Posts
    514

    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 at 01:58.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th April 2006
    Posts
    890

    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.

    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by bryce; October 26th, 2006 at 02:15.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    25th April 2005
    Location
    Oakland,New Jersey
    Posts
    514

    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 at 02:17.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    25th April 2006
    Posts
    890

    Re: Unhide Sheet Based On Cell Value Contents

    Try this one then.

    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th April 2005
    Location
    Oakland,New Jersey
    Posts
    514

    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!!!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    22nd July 2013
    Posts
    1

    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"?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    7th August 2014
    Posts
    1

    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 at 06:42. Reason: New thought

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 8
    Last Post: January 13th, 2009, 03:33
  2. Unhide/Hide Sheet Based On Value In Cell
    By caliskier in forum EXCEL HELP
    Replies: 6
    Last Post: April 3rd, 2008, 07:36
  3. Hide/Unhide Columns Based On Cell Value
    By ss_bb_24 in forum EXCEL HELP
    Replies: 13
    Last Post: September 6th, 2007, 17:16
  4. Hide/Unhide Sheets only working once...
    By Gra in forum EXCEL HELP
    Replies: 8
    Last Post: November 8th, 2005, 20:32
  5. VBA : Hide / Unhide Sheets
    By TimBray in forum EXCEL HELP
    Replies: 2
    Last Post: April 16th, 2004, 18:02

Bookmarks

Posting Permissions

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