vba 如何显示多选列表框中的值

How to display Values from a multiselect listbox

提问by user3175856

I have a form in Excel macro. This form will capture the values inserted in textboxes, listbox and store in Sheet2.

我在 Excel 宏中有一个表单。此表单将捕获插入到文本框、列表框和存储在 Sheet2 中的值。

There are 2 buttons in the form applet named "Next" and "Previous". These button will be used for navigating between the saved records. I am able to navigate between records and the values display fine in textboxes. However, I am not sure how can I display the Values from listboxes. My list box is a multiselect list box.

表单小程序中有 2 个按钮，名为“Next”和“Previous”。这些按钮将用于在保存的记录之间导航。我能够在记录之间导航，并且值在文本框中显示良好。但是，我不确定如何显示列表框中的值。我的列表框是一个多选列表框。

I have provided snippet of my code on how the records are saved in sheet2 and how the navigation happens when clicked on Next button.

我已经提供了关于如何将记录保存在 sheet2 中以及单击“下一步”按钮时导航如何发生的代码片段。

     Private Sub Save_Click()

      Dim ctl As Control
      Dim S As String
      Dim i As Integer


      RowCount = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
     With Worksheets("Sheet2").Range("A1")
    .Offset(RowCount, 0).Value = Me.Name1.Value  ' capture value from list box

     'below code is for capturing value from multiselect listbox 
     With AOI
      For i = 0 To .ListCount - 1
         If .Selected(i) = True Then S = S & ", " & .List(i)
     Next i
      Range("A1").Offset(RowCount, 10).Value = S
     End With
    End Sub

Below code is for navigating between saved records..

下面的代码用于在保存的记录之间导航..

   Private Sub Next1_Click()

      strCurrentSetofRows = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count
      i = i + 1: j = 0

      If i > (strCurrentSetofRows - 1) Then
        MsgBox "No More Records"
      Exit Sub
      End If

      Set sRange = Worksheets("Sheet2").Range("A1")

      Name1.Text = sRange.Offset(i, j).Value: j = j + 1
   End Sub

Any thoughts on how can I display saved values of AOI (my field).

关于如何显示 AOI（我的领域）的保存值的任何想法。

回答by Siddharth Rout

Since you are storing the values using ,as a separator, you can use the same to split the values and upload it to the ListBox. BTW, I hope you are generating the ListBoxwith the complete list in the UserForm'sInitializeevent?

由于您使用,作为分隔符来存储值，因此您可以使用它来拆分值并将其上传到ListBox. 顺便说一句，我希望您ListBox在UserForm'sInitialize事件中生成完整列表？

Here is a very basic example. Please amend it to suit your needs.

这是一个非常基本的例子。请修改它以满足您的需要。

Let's say Cell A1has Blah1,Blah2,Blah6. Then try this code

假设 CellA1有Blah1,Blah2,Blah6. 然后试试这个代码

Option Explicit

Dim i As Long, j As Long

Private Sub UserForm_Initialize()
    ListBox1.MultiSelect = fmMultiSelectMulti

    For i = 1 To 10
        ListBox1.AddItem "Blah" & i
    Next
End Sub

Private Sub CommandButton1_Click()
    Dim ArValues As Variant
    Dim sValue As String
    Dim multivalues As Boolean

    If InStr(1, Range("A1").Value, ",") Then
        ArValues = Split(Range("A1").Value, ",")
        multivalues = True
    Else
        sValue = Range("A1").Value
        multivalues = False
    End If

    If multivalues = True Then
        For i = 0 To UBound(ArValues)
            For j = 0 To ListBox1.ListCount - 1
                If ListBox1.List(j) = ArValues(i) Then
                    ListBox1.Selected(j) = True
                    Exit For
                End If
            Next j
        Next i
    Else
        For j = 0 To ListBox1.ListCount - 1
            If ListBox1.List(j) = sValue Then
                ListBox1.Selected(j) = True
                Exit For
            End If
        Next j
    End If
End Sub

Screenshot

截屏