Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

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


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