Forum Discussion

Shipwreck818's avatar
Shipwreck818
Copper Contributor
Oct 23, 2023

Hide Tabs based on Value of cell range

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.

Excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 23, 2023

    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
    • Shipwreck818's avatar
      Shipwreck818
      Copper Contributor
      Oct 24, 2023

      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.