Page 1 of 2 12 LastLast
Results 1 to 10 of 11

VBA to Hide/Unhide sheets based on cell value

This is a discussion on VBA to Hide/Unhide sheets based on cell value within the Excel Questions forums, part of the Question Forums category; I have a workbook which has roughly 50 sheets. What I'm trying to do is automatically hide/unhide sheets based on ...

  1. #1
    New Member SnapwireMan's Avatar
    Join Date
    Jan 2008
    Location
    Texas
    Posts
    10

    Default VBA to Hide/Unhide sheets based on cell value

    I have a workbook which has roughly 50 sheets. What I'm trying to do is automatically hide/unhide sheets based on the cell values in the first sheet. So in sheet1 cell A1 i would a value of FALSE which would trigger sheets1, 2, & 3 to hide, when that value changes to TRUE then those same sheets would unhide. I need to replicate that for the 10 corresponding sets of sheets, but for each grouping of sheets a different cell in sheet1 would be the trigger, cell A2 = sheets 4 - 10, cell A3 = sheets 11 - 20, etc. Any help on the VBA code would be greatly appreciated. Thanks everyone!

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,333

    Default Re: VBA to Hide/Unhide sheets based on cell value

    Welcome to the board...

    Do you mean litterally sheets 1-3 (by the index # of the sheet), or do you have specific names for the sheets,
    like A1 is for "Jons Sheet", "Freds Sheet", and "Barneys Sheet" ?

    If the latter is true, specific sheet names, You'll need to setup a lookup table to tell the code which sheets are attached to which cell..
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,333

    Default Re: VBA to Hide/Unhide sheets based on cell value

    If you mean by the Index # of the sheets, This should do...

    Code:
    Set MasterSheet = Sheets("Sheet With Cell Refs In it")
    for Each C in MasterSheet.Range("A1:A10")
        Select Case C.Address(0,0)
            Case "A1"
                F = 1
                T = 3
            Case "A2"
                F = 4
                T = 10
            Case "A3"
                F = 11
                T = 20
        End Select
        For i = F to T
            Sheets(i).Visible = C.Value = TRUE
        Next i
    Next C
    Hope this Helps
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    New Member SnapwireMan's Avatar
    Join Date
    Jan 2008
    Location
    Texas
    Posts
    10

    Default Re: VBA to Hide/Unhide sheets based on cell value

    Even though I've changed the name of the sheets, I think I can use the literal name of the sheet (sheet2) since each sheet that I'm going to hide/unhide is in sequential order. If not, i will have to build a lookup table as you suggested.

    Where do I put the code in the VBA editor? ThisWorkbook, Sheet1, is it an object? I'm a novice when it comes to VBA.....thanks for the quick reply.

  5. #5
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,333

    Default Re: VBA to Hide/Unhide sheets based on cell value

    I think I can use the literal name of the sheet (sheet2) since each sheet that I'm going to hide/unhide is in sequential order
    Actually, if your sheet names are in sequential order, it would be easier to use the Index # instead of sheet names.

    In the VBA window, look at the order of the sheets there, are they numbered correctly for your 1-3 4-10 11-20 Order??

    If so, you can paste this code into a standard module (Click Insert - Module)
    Then you will have a macro named Test that you can run

    Code:
    Sub Test()
    Set MasterSheet = Sheets("Sheet With Cell Refs In it")
    for Each C in MasterSheet.Range("A1:A10")
        Select Case C.Address(0,0)
            Case "A1"
                F = 1
                T = 3
            Case "A2"
                F = 4
                T = 10
            Case "A3"
                F = 11
                T = 20
        End Select
        For i = F to T
            Sheets(i).Visible = C.Value = TRUE
        Next i
    Next C
    End Sub
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  6. #6
    New Member SnapwireMan's Avatar
    Join Date
    Jan 2008
    Location
    Texas
    Posts
    10

    Default Re: VBA to Hide/Unhide sheets based on cell value

    That worked great....thank you! What would be the best way to automate this code?

  7. #7
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,333

    Default Re: VBA to Hide/Unhide sheets based on cell value

    Glad to help..

    What would be the best way to automate this code?
    Several options for that, but will depend on more specific details...

    When would you like the code to auto run ?
    When you open the book?
    When you close or save the book?
    When a Specific sheet is selected
    When a specific Range is changed (is that by formula or by manual user entry)?
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  8. #8
    New Member SnapwireMan's Avatar
    Join Date
    Jan 2008
    Location
    Texas
    Posts
    10

    Default Re: VBA to Hide/Unhide sheets based on cell value

    It needs to run when the specific range is changed,that range being the same range in the MasterSheet (A1:A10). The information in this range is neither a formula nor manual entry, it's actually a feed coming from a SQL server database which is updated from a 3rd party system.

  9. #9
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,333

    Default Re: VBA to Hide/Unhide sheets based on cell value

    it's actually a feed coming from a SQL server database which is updated from a 3rd party system.
    Unfortunately you may not be able to automate it. Some (most) 3rd party applications do not trigger events in excel vba. So the VBA code may not detect when those cells change...
    But, it might, so here's how it would work..

    In the sheet that has the range A1:A10...
    Right click that sheet's tab, view code

    Paste the following...

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
    For Each C In Range("A1:A10")
        Select Case C.Address(0, 0)
            Case "A1"
                F = 1
                T = 3
            Case "A2"
                F = 4
                T = 10
            Case "A3"
                F = 11
                T = 20
        End Select
        For i = F To T
            Sheets(i).Visible = C.Value = True
        Next i
    Next C
    End Sub
    HTH
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  10. #10
    New Member SnapwireMan's Avatar
    Join Date
    Jan 2008
    Location
    Texas
    Posts
    10

    Default Re: VBA to Hide/Unhide sheets based on cell value

    That worked like a champ, the 3rd party application passed the data over without any issues. Thank you very much, that's a huge help!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com