SOLVED

Hide Tabs based on Value of cell range

Copper Contributor

I have a work project crating an excel sheet for the purposes of a legal proocedure based on a date range entered in Sheet2 (FC4007.5).

 

The date range entered, as many as 1, to 7 variables are shown (visible ) and the Columns are either hidden or visible based on the date ranage.

 

So here is my issue, I hope to make each variable "TAB" do the same. I want to make every tab hidden or visible based on the applicble versions shown in Sheet2 (FC4007.5).

 

Reason is each tab will have a flow chart of instructions for a legal procedure which is quite large for each tab. I do not want to crwod Sheet 2, hence each version having its own tab on policy and procedure.

 

Thank you in advance for your time.

4 Replies

@Shipwreck818 

Change the Worksheet_Change event procedure of the FC4007.5 sheet to

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim wsh As Worksheet
    If Not Intersect(Range("B2:B3"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Columns("D:Q").EntireColumn.Hidden = True
        For Each wsh In Worksheets(Array("Pre-Version 1", _
                "Version 1 (SB 1355)", "Repeal Period 1", _
                "Version 2 (AB 610)", "Repeal Period 2", _
                "Version 3 (AB 2325)", "Current (AB 207)"))
            wsh.Visible = xlSheetHidden
        Next wsh
        For Each rng In Range("B5:B15")
            If rng.Value <> "" Then
                Worksheets(rng.Value).Visible = xlSheetVisible
            End If
            Select Case rng.Value
                Case "Pre-Version 1"
                    Columns("D:E").EntireColumn.Hidden = False
                Case "Version 1 (SB 1355)"
                    Columns("F:G").EntireColumn.Hidden = False
                Case "Repeal Period 1"
                    Columns("H:I").EntireColumn.Hidden = False
                Case "Version 2 (AB 610)"
                    Columns("J:K").EntireColumn.Hidden = False
                Case "Repeal Period 2"
                    Columns("L:M").EntireColumn.Hidden = False
                Case "Version 3 (AB 2325)"
                    Columns("N:O").EntireColumn.Hidden = False
                Case "Current (AB 207)"
                    Columns("P:Q").EntireColumn.Hidden = False
            End Select
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Thank you for your response.
Forgive my lack of vba coding, but is this ment to overide the previous vode i have?
I applied this one and I am getting error code plus it hide all my rows regardless of the date entered.

Shipwreck818_1-1698110898066.png

 

 

 

best response confirmed by Shipwreck818 (Copper Contributor)
Solution

@Shipwreck818 

My apologies, I forgot to mention that you should rename the sheet Pre Version 1 to Pre-Version 1 to be consistent with the spelling on the FC4007.5 sheet.

See the attached version

Perfect , excatly what I needed. Thank you so very much!
1 best response

Accepted Solutions
best response confirmed by Shipwreck818 (Copper Contributor)
Solution

@Shipwreck818 

My apologies, I forgot to mention that you should rename the sheet Pre Version 1 to Pre-Version 1 to be consistent with the spelling on the FC4007.5 sheet.

See the attached version

View solution in original post