从工作表上的列表框中获取值 [英] Get values from a listbox on a sheet

问题描述

我在Excel工作簿的Sheet1上有一个名为ListBox1的列表框.

I have a listbox named ListBox1 on Sheet1 of an Excel workbook.

每次用户选择列表中的一项时，我需要将其名称复制到名为strLB的变量中.

Every time the user selects one of the items in the list, I need to copy its name to a variable named strLB.

因此，如果我具有Value1，Value2，Value3，Value4，并且用户选择了Value1和Value3，则我的strLB必须以Value1，Value3的形式出现.

So, if I have Value1, Value2, Value3, Value4 and the user selects Value1 and Value3, I need my strLB to come out as Value1,Value3.

我尝试通过以下方式进行事后:

I tried doing that post hoc with:

For i = 1 To ActiveSheet.ListBoxes("ListBox1").ListCount
    If ActiveSheet.ListBoxes("ListBox1").Selected(i) Then strLB = strLB & etc.etc.
Next i

但这非常慢(我的列表框中有15k值).这就是为什么在用户完成输入后，我需要实时而不是周期性地记录选择内容的原因.

But this is very slow (I have 15k values in my listbox). This is why I need to record the selection in real time and not in a cycle, after the user is done inputting.

我还将需要一种方法来检查用户是否删除了之前的任何选择.

I'm going to also need a way to check if the user removed any of the previous selection.

推荐答案

不幸的是，对于MSForms列表框，循环遍历列表项并检查其Selected属性是唯一的方法.但是，这是另一种选择.我正在将选定的项目存储/删除到一个变量中，您可以在某个远程单元格中进行此操作并跟踪它:)

Unfortunately for MSForms list box looping through the list items and checking their Selected property is the only way. However, here is an alternative. I am storing/removing the selected item in a variable, you can do this in some remote cell and keep track of it :)

Dim StrSelection As String

Private Sub ListBox1_Change()
    If ListBox1.Selected(ListBox1.ListIndex) Then
        If StrSelection = "" Then
            StrSelection = ListBox1.List(ListBox1.ListIndex)
        Else
            StrSelection = StrSelection & "," & ListBox1.List(ListBox1.ListIndex)
        End If
    Else
        StrSelection = Replace(StrSelection, "," & ListBox1.List(ListBox1.ListIndex), "")
    End If
End Sub

这篇关于从工作表上的列表框中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！