excel - 如何在用户表单中进行多选列表框选择？

Question

我有一个带有多选列表框的用户表单。

我编写的代码希望用户选择两个选项。

我需要用户只有在没有选择至少一个选项时才会收到错误消息。

我从单选列表框错误消息的代码开始。我试着让它 >= -1 <0 <>-1 并且它们都不允许用户只选择一个选项。

有问题的行是：

If ListBoxNextSteps.ListIndex <> 1 Then
    MsgBox "Please select next steps"
    Exit Sub
End If

---

Private Sub CommandButtonSubmit_Click()

    'Requires specific fields to be complete before user can submit
    If ComboBoxDBN = "" Then
        MsgBox "Please select a DBN"
        Exit Sub
    End If

    If TextBoxDate = "" Then
        MsgBox "Plese enter a date"
        Exit Sub
    End If

    If CheckBoxCohort = False Then

        If TextBoxContactName = "" Then
            MsgBox "Please list school officials that you contacted"
            Exit Sub
        End If

        If ListBoxSupportType.ListIndex = -1 Then
            MsgBox "Please select a support type"
            Exit Sub
        End If

        If TextBoxDiscussion = "" Then
            MsgBox "Please describe your discussion points"
            Exit Sub
        End If

        If TextBoxLearn = "" Then
            MsgBox "Please describe what you learned about the school's challenges"
            Exit Sub
        End If

        If (CheckBoxAdminDiff + CheckBoxConflict + CheckBoxShortage + CheckBoxDataSystems + CheckBoxOther) = 0 Then
            MsgBox "Please select at least one bucket"
            Exit Sub
        End If

        If (CheckBoxOther = True And TextBoxIfOther = "") Then
            MsgBox "Please describe other bucket"
            Exit Sub
        End If

        If ListBoxNextSteps.ListIndex <> 1 Then
            MsgBox "Please select next steps"
            Exit Sub
        End If

        If ListBoxResolution.ListIndex = -1 Then
            MsgBox "Please select a resolution status"
            Exit Sub
        End If

    End If


    'tells form to put responses in a long table
    Dim emptyRow As Long

    'Make Sheet1 (SchoolSupport) active so it knows where to put the responses
    Sheet1.Activate
    ActiveSheet.Protect UserInterfaceOnly:=True, AllowFiltering:=True


    'Determine emptyRow so it knows where the next entry goes
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
    'Transfer information from form fields to table cells
    Cells(emptyRow, 1).Value = ComboBoxDBN.Value
    Cells(emptyRow, 2).Value = TextBoxDate.Value
    Cells(emptyRow, 3).Value = TextBoxContactName.Value
    Cells(emptyRow, 6).Value = ListBoxSupportType.Value
    Cells(emptyRow, 7).Value = TextBoxDiscussion.Value
    Cells(emptyRow, 8).Value = TextBoxBestPractice.Value
    Cells(emptyRow, 9).Value = TextBoxLearn.Value
    Cells(emptyRow, 15).Value = TextBoxIfOther.Value
    'Makes sure multiple selections appear in same cell
    If CheckBoxCohort = False Then
        Dim s As String, i As Integer
            With ListBoxNextSteps
                For i = 0 To .ListCount - 1
                    If .Selected(i) = True Then s = s & .List(i) & ", "
                Next i
            End With
        With Cells(emptyRow, 16)
            .Value = Left(s, Len(s) - 1)
        End With
    End If

    Cells(emptyRow, 17).Value = ListBoxResolution.Value
    Cells(emptyRow, 18).Value = TextBoxEscalateTo.Value
    Cells(emptyRow, 19).Value = ListBoxEscalateLocation.Value
    Cells(emptyRow, 20).Value = ListBoxEscalateStatus.Value
    Cells(emptyRow, 21).Value = TextBoxPertinentNotes.Value

    If CheckBoxUnresponsive.Value = True Then Cells(emptyRow, 4).Value = "Y"
    If CheckBoxCohort.Value = True Then Cells(emptyRow, 5).Value = "Y"
    If CheckBoxAdminDiff.Value = True Then Cells(emptyRow, 10).Value = "Y"
    If CheckBoxConflict.Value = True Then Cells(emptyRow, 11).Value = "Y"
    If CheckBoxShortage.Value = True Then Cells(emptyRow, 12).Value = "Y"
    If CheckBoxDataSystems.Value = True Then Cells(emptyRow, 13).Value = "Y"
    If CheckBoxOther.Value = True Then Cells(emptyRow, 14).Value = "Y"

    'Saves workbook
    Application.ActiveWorkbook.Save

    Unload SchoolSupportForm

End Sub

我希望 ListBoxNextSteps 错误消息仅在用户未进行任何选择时出现，而是在用户未进行至少 2 次选择时出现。

标签： excelvbalistboxuserform

Answer 1

您可以使用 ListBox 对象的 Selected 方法来检查项目是否被选中。以下代码循环遍历列表框中的每个项目。如果选择了一个项目，则布尔变量 itemSelected 设置为 True 并退出 For/Next 循环。退出循环后，如果 itemSelected 设置为 False，则显示消息，然后退出子程序。

Dim itemSelected As Boolean
Dim i As Long

itemSelected = False
With Me.ListBoxNextSteps
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            itemSelected = True
            Exit For
        End If
    Next i
End With

If Not itemSelected Then
    MsgBox "Please select next steps"
    Exit Sub
End If