VBA to Hide/Unhide sheets based on cell value

[SnapwireMan]

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!

 

[Jonmo1]

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

 

[Jonmo1]

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

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

 

[SnapwireMan]

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.

 

[Jonmo1]

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

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

 

[SnapwireMan]

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

 

[Jonmo1]

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

 

[SnapwireMan]

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.

 

[Jonmo1]

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

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<!-- / message --><!-- sig -->

 

[SnapwireMan]

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!