excel - 在组合框中显示可见工作表

Question

您好我有以下代码在组合框中显示工作表。工作表在创建时会自动添加到列表中。

我不希望隐藏的工作表在 Dropbox 中可见。我怎么做？

Option Explicit

Private Sub ComboBox1_Change()
    If ComboBox1.ListIndex > -1 Then Sheets(ComboBox1.Text).Select
End Sub

Private Sub Combobox1_DropbuttonClick()
    Dim xSheet As Worksheet
    On Error Resume Next
    Application.screenupdateing = False
    Application.EnableEvents = False
    If ComboBox1.ListCount <> ThisWorkbook.Sheets.Count Then
        ComboBox1.Clear
        For Each xSheet In ThisWorkbook.Sheets
            ComboBox1.AddItem xSheet.Name
            Next xSheet
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Private Sub Combobx1_Gotfocus()
    If ComboBox1.ListCount <> 0 Then ComboBox1.DropDown
End Sub

Answer 1

这是你正在尝试的吗？

Private Sub Combobox1_DropbuttonClick()
    Dim xSheet As Worksheet

    '~~> Clear the bocmbobox
    ComboBox1.Clear

    '~~> Add the sheet again
    For Each xSheet In ThisWorkbook.Sheets
        '~~> Check if they are visible
        If xSheet.Visible = xlSheetVisible Then
            ComboBox1.AddItem xSheet.Name
        End If
    Next
End Sub