VBA从Excel中的电子表格中的列表框中获取值 [英] VBA to get values from a listbox on a spreadsheet in Excel

问题描述

每当用户选择列表中的一个项目时，我需要将其名称复制到一个名为strLB的变量。所以，如果我有Value1，Value2，Value3，Value4和用户选择Value1和Value3，我需要我的strLB作为Value1，Value3出来。相当直截了当。

我尝试用 post hoc ：

 对于i = 1到ActiveSheet.ListBoxes（ListBox1）。ListCount 
如果ActiveSheet.ListBoxes（ListBox1）。Selected（i）Then strLB = strLB& etc.etc。 
下一个i 
  

但是这非常慢（我的列表框中实际上有15k的值） 。这就是为什么我需要在用户完成输入后实时记录选择，而不是在循环中。

当然，我也需要一个

希望你们可以帮助!!

解决方案

不幸的是，MSForms列表框循环遍历列表项，并检查其Selected属性是唯一的方法。但是，这里有一个选择。我正在将所选项目存储/删除到变量中，您可以在一些远程单元格中进行操作，并跟踪它：）

  $ b如果ListBox1.Selected（ListBox1.ListIndex）然后
如果StrSelection =然后
 StrSelection = ListBox1。 List（ListBox1.ListIndex）
 Else 
 StrSelection = StrSelection& ，& ListBox1.List（ListBox1.ListIndex）
 End If 
 Else 
 StrSelection = Replace（StrSelection，，& ListBox1.List（ListBox1.ListIndex），）
结束如果
结束Sub 
  

HTH

Sid

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

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

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. Pretty straightforward.

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

But this is very slow (I actually 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.

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

Hope you guys can help!!

解决方案

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

HTH

Sid

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