Results 1 to 3 of 3

Show / Hide Sheets based off a cell input

Threaded View

  1. #1
    Registered User
    Join Date
    10-15-2012
    Location
    Cary
    MS-Off Ver
    Excel 2003
    Posts
    18

    Show / Hide Sheets based off a cell input

    I've seen several ways to hide sheets based off a determined value. The way I have been doing it is like the following:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sWord As Integer
    sWord = Sheets("Master").Range("B8").Value
    
    If sWord = "4" Then
        Sheets("CofA1").Visible = True
        Sheets("CofA2").Visible = True
        Sheets("CofA3").Visible = True
        Sheets("CofA4").Visible = True
        Sheets("CofA5").Visible = False
        Sheets("CofA6").Visible = False
        Sheets("CofA7").Visible = False
        Sheets("CofA8").Visible = False
        Sheets("CofA9").Visible = False
        Sheets("CofA10").Visible = False
        Sheets("CofA11").Visible = False
        Sheets("CofA12").Visible = False
        Sheets("CofA13").Visible = False
        Sheets("CofA14").Visible = False
        Sheets("CofA15").Visible = False
        Sheets("CofA16").Visible = False
        Sheets("CofA17").Visible = False
        Sheets("CofA18").Visible = False
        Sheets("CofA19").Visible = False
        Sheets("CofA20").Visible = False
    ElseIf sWord = "5" Then
    .....
    EndIf
    This is clearly cumbersome and could be polished up with a loop. I am not skilled enough ... yet ... to put this code together. Can someone give me a hand in doing this? The spark for this thread came from my code being too long when trying to introduce 40 sheets and the code not executing at all.

    Thanks in advance

    Evan
    Last edited by Cutter; 10-16-2012 at 02:09 PM. Reason: Added code tags

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1