excel - ListBox.Selected 不起作用；运行时错误'-2147024809(80070057)

Question

如果在ListBox. ListBox.Selected(c)是说无法获得选定的属性。它有运行时错误，如果我结束而不是调试，它正在隐藏或取消隐藏一些工作表。

我尝试使用ListIndex或ListValue代替，ListCount但它不会去任何地方！

Private Sub CommandButton1_Click()
Dim str As String
Dim Status As String

With ListBox1

Dim C As Long
For C = 1 To ListBox1.ListCount

str = ListBox1.Column(1, ListBox1.ListIndex)


If ListBox1.Selected(C) = True And str = "Visible" Then
Sheets(C).Visible = False

ElseIf ListBox1.Selected(C) = False And str = "Visible" Then
Sheets(C).Visible = True
End If

If ListBox1.Selected(C) = True And str = "Invisible" Then
Sheets(C).Visible = True

ElseIf ListBox1.Selected(C) = False And str = "Invisible" Then
Sheets(C).Visible = False
End If

Next C
Unload Me
End With

End Sub

当列表框项目被选中时，在点击命令按钮时，它会将工作表的可见性状态从隐藏更改为取消隐藏，反之亦然。列表框

标签： excelvbalistboxhide

Answer 1

列表框计数从零开始，因此当您选择列表中的第一项时，它永远不会到达，但是Sheets从 1 开始，因此您需要始终将 1 添加到列表框索引才能到达它。

编辑：

Private Sub CommandButton1_Click()

    Dim c As Long
    dim intListCount as Long

    intListCount = Me.ListBox1.ListCount - 1

    For c = 0 To intListCount

        ThisWorkbook.Sheets(c + 1).Visible = IIf(Me.ListBox1.Selected(c) And Me.ListBox1.List(c, 1) = "Visible", False, True)

    Next c

    Unload Me

End Sub

Private Sub UserForm_Initialize()

    Dim ws As Worksheet

    i = 0
    For Each ws In ThisWorkbook.Worksheets

        Me.ListBox1.AddItem
        Me.ListBox1.List(i, 0) = ws.Name
        Me.ListBox1.List(i, 1) = IIf(Not ws.Visible, "Hidden", "Visible")

    i = i + 1
    Next ws

End Sub

请注意，当您尝试隐藏工作簿中的所有可用工作表时，可能会提示错误。