Excel VBA基于表中单元格的值显示/隐藏工作表

rkkpypqq 于 2023-01-06 发布在其他

关注(0)|答案(2)|浏览(218)

我正在尝试合并一个更改事件，如果表列包含特定值，该事件将显示/隐藏特定选项卡。
我的表名为Table18，我需要检查两列中的值，这两列名为"Group"（第2列）和"自定义类型"（第4列）。下面的代码中列出了要显示/隐藏的工作表。下面的代码中还列出了所需的值，这些值必须位于"组"或"自定义类型"列中，才能显示工作表。如果该值不存在，则我希望隐藏该工作表。
我尝试的代码是工作的，但只有表的最后一行。我有一个工作表上的更改事件调用以下模块
因此，总的来说，我希望：
1.以便跟踪表的多行，并在发生更改时根据需要进行更新
1.是否有办法引用工作表名称而不是编号，即工作表"Group 1"而不是"Sheet4"？
1.使用一个rng，而不是rng和rng2
1.任何缩短或清理代码的建议
谢谢!

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

excel

来源：https://stackoverflow.com/questions/75016422/excel-vba-show-hide-worksheets-based-on-cell-values-in-table

2条答案

按热度按时间

wrrgggsh1#

根据您的问题，下面的代码使用WorksheetFunction.Countif检查整列是否包含1，并根据此更改所需工作表的可见性。这是否满足您的需要？

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

编辑：鉴于您已经编辑了问题，更新了下面的代码建议。您的代码只作用于表的最后一行，因为它循环通过列中的每个单元格，并基于该单元格决定是否显示或隐藏各个工作表。因此，每个单元格的操作都会被后面的操作覆盖-根据我上面的建议，您应该使用WorksheetFunction.Countif来检查整列中的任何位置是否出现1。
我将隐藏表过程拆分为一个单独的子过程，而不是重复代码，使其更易于查看，并删除了许多循环。
编辑代码如下：

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

赞(0）回复(0）举报 2023-01-06

rqenqsqc2#

只是一个一般的事情，以缩短您的代码以上/使其更容易阅读。
这个也应该这样做。

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

如果默认情况下工作表可见，则还可以删除所有“xlSheetVisible”语句
...作为代码的这一部分：

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

赞(0）回复(0）举报 2023-01-06

我来回答

Excel VBA基于表中单元格的值显示/隐藏工作表

2条答案

相关问题

热门标签

最新问答