Excel VBA Show/Hide worksheets based on cell values in table-CodePudding

I am trying to incorporate a change event which will show/hide a particular tabs if table columns includes a specific values.

My table is called Table18 and I need to check values in two columns. They are called "Group" (Col 2) and "Custom Type" (Col 4). The worksheets I want it to show/hide are listed in the code below. The values required are also listed in the code below and must either be in the Group or Custom Type columns for the worksheets to show, if the value isn't present then I want that worksheet to be hidden.

The code I tried is working but only for the last line of the table. I have a change event on the worksheet that calls the below module

So, in summary, I would like the following:

For it to track multiple lines of the table and update as required when a change is made
Is there a way to refer to the sheet name instead of number i.e. sheet "Group 1" instead of "Sheet4"?
To have one rng, instead of rng and rng2
Any recommendations to shorten or clean up the code

Thanks!

Sub TabDisplay()
    Dim tbl As ListObject
    Dim rng As Range
    Set tbl = ActiveSheet.ListObjects("Table18")
    Set rng = tbl.ListColumns(2).DataBodyRange
    Set rng2 = tbl.ListColumns(4).DataBodyRange

    Dim cl As Range
    For Each cl In rng
        If cl.Value = 1 Then
            Sheet4.Visible = xlSheetVisible
        Else
            Sheet4.Visible = xlSheetHidden
        End If
    Next
        For Each cl In rng
        If cl.Value = 2 Then
            Sheet7.Visible = xlSheetVisible
        Else
            Sheet7.Visible = xlSheetHidden
        End If
    Next
    For Each cl In rng
        If cl.Value = 3 Then
            Sheet9.Visible = xlSheetVisible
        Else
            Sheet9.Visible = xlSheetHidden
        End If
    Next
    For Each cl In rng
        If cl.Value = 3 Then
            Sheet10.Visible = xlSheetVisible
        Else
            Sheet10.Visible = xlSheetHidden
        End If
    Next
    For Each cl In rng2
        If cl.Value = "Business Division" Then
            Sheet11.Visible = xlSheetVisible
        Else
            Sheet11.Visible = xlSheetHidden
        End If
    Next
    For Each cl In rng2
        If cl.Value = "Complexity" Then
            Sheet12.Visible = xlSheetVisible
        Else
            Sheet12.Visible = xlSheetHidden
        End If
    Next
    For Each cl In rng2
        If cl.Value = "Location" Then
            Sheet14.Visible = xlSheetVisible
        Else
            Sheet14.Visible = xlSheetHidden
        End If
    Next
End Sub

CodePudding user response：

Based on what your question is asking for, the below uses WorksheetFunction.Countif to check the entire column to see if it contains a 1, and changes the desired sheet's visibility based on that. Does this meet your needs?

Private Sub Worksheet_Change(ByVal Target As Range)
  
Dim intersectRange As Range, sheetHide As Worksheet
Set intersectRange = Range("Table18[Group]")
Set sheetHide = Sheets("Schedule & FTE (Group 1)")

If Not Intersect(Target, intersectRange) Is Nothing Then
    sheetHide.Visible = WorksheetFunction.CountIf(intersectRange, 1) > 0
End If

End Sub

EDIT: Seeing as you've edited the question, updated code suggestion below. Your code is only acting on the last row of the table because it's looping through every single cell in the columns and deciding based on that one cell whether to show or hide the various sheets. Hence, each cell's action gets overwritten by the one after - as per my suggestion above you should use WorksheetFunction.Countif to check whether a 1 appears anywhere in the entire column.

I've split out the hide-sheet process into a separate sub rather than repeat the code, makes it simpler to look at and removes many loops.

Edited code below:

Sub TabDisplay()
    Dim tbl As ListObject
    Dim rng As Range, rng2 As Range
    Set tbl = ActiveSheet.ListObjects("Table18")
    Set rng = tbl.ListColumns(2).DataBodyRange
    Set rng2 = tbl.ListColumns(4).DataBodyRange

    Call HideSheetIfFindValue(rng, 1, Sheets("Group 1"))
    Call HideSheetIfFindValue(rng, 2, Sheet7)
    Call HideSheetIfFindValue(rng, 3, Sheet9)
    Call HideSheetIfFindValue(rng, 3, Sheet10)
    Call HideSheetIfFindValue(rng2, "Business Division", Sheet11)
    Call HideSheetIfFindValue(rng2, "Complexity", Sheet12)
    Call HideSheetIfFindValue(rng2, "Location", Sheet14)
End Sub

Private Sub HideSheetIfFindValue(Look_In As Range, Look_for As Variant, Hide_Sheet As Worksheet)
Hide_Sheet.Visible = WorksheetFunction.CountIf(Look_In, Look_for) < 1
End Sub

CodePudding user response：

Just a general thing to shorten your code above / make it easier readable.

this shoud do the same ...

For Each cl In rng
    Select Case cl.Value
    Case 1
        Sheet4.Visible = xlSheetVisible
        Sheet7.Visible = xlSheetHidden
        Sheet9.Visible = xlSheetHidden
        Sheet10.Visible = xlSheetHidden
    Case 2
        Sheet7.Visible = xlSheetVisible
        Sheet4.Visible = xlSheetHidden
        Sheet9.Visible = xlSheetHidden
        Sheet10.Visible = xlSheetHidden
    Case 3
        Sheet9.Visible = xlSheetVisible
        Sheet10.Visible = xlSheetVisible
        Sheet4.Visible = xlSheetHidden
        Sheet7.Visible = xlSheetHidden
    Case Else
        Sheet4.Visible = xlSheetHidden
        Sheet7.Visible = xlSheetHidden
        Sheet9.Visible = xlSheetHidden
        Sheet10.Visible = xlSheetHidden
    End Select
Next

if your Worksheets are visible by default you could also delete all "xlSheetVisible" statements"

... as this part of your code:

   For Each cl In rng
        If cl.Value = 1 Then
            Sheet4.Visible = xlSheetVisible
        Else
            Sheet4.Visible = xlSheetHidden
        End If
    Next
        For Each cl In rng
        If cl.Value = 2 Then
            Sheet7.Visible = xlSheetVisible
        Else
            Sheet7.Visible = xlSheetHidden
        End If
    Next
    For Each cl In rng
        If cl.Value = 3 Then
            Sheet9.Visible = xlSheetVisible
        Else
            Sheet9.Visible = xlSheetHidden
        End If
    Next
    For Each cl In rng
        If cl.Value = 3 Then
            Sheet10.Visible = xlSheetVisible
        Else
            Sheet10.Visible = xlSheetHidden
        End If 
    Next