如何解决生成的列表框并在VBA中动态添加项目? [英] How to address generated ListBoxes and add Items dynamically in VBA?

问题描述

我成功地成功地动态生成了列表框.但是我现在正在努力解决并填充那些生成的ListBox.此外，我不知道如何激活那些ListBoxes的MultiSelect属性.只能使用ActiveX吗?

I successfully managed to generate ListBoxes dynamically. But I am now struggling with addressing and populating those generated ListBoxes. Additionally, I can't figure out how to activate the MultiSelect Property of those ListBoxes. Is that only possible with ActiveX?

我首先尝试了ActiveX-userForm上的ListBoxes.现在，我在WorkSheet上切换回普通"列表框. "FS"是我正在处理的工作表的名称.为了理解:我遍历工作表FS上的列，并为每个列创建一个ListBox.在每个列表框中，将添加相应列的条目.

I first tried ActiveX - ListBoxes on userForm. Now I switched back to "normal" ListBoxes on the WorkSheet. "FS" is the name of my Worksheet which I am working on. For understanding: I am looping through the columns on worksheet FS and creating one ListBox per Column. In each ListBox the entries of the according column will be added.

For i = 1 To 10
    LastRow = FS.Cells(Rows.Count, i).End(xlUp).Row
        With FS
            Set lb = FS.Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)
            lb.ControlFormat.MultiSelect = 2
            For Each cell In FS.Range(Cells(1, i), Cells(LastRow,i)).Cells
                    lb.ControlFormat.AddItem cell.Value 'This is the problematic line
            Next cell
        End With
Next i

推荐答案

我建议您这样做:

Sub test()
''''Declarations'''''''''''''''''''''''''''
Dim lb As ListBox                           '
Dim sht As Worksheet                        '
Dim rng As Range                            '
Dim cell As Range                           '
Dim i As Long                               '
'''''''''''''''''''''''''''''''''''''''''''''
Set sht = ThisWorkbook.Worksheets("Name of your worksheet")
For i = 1 To 10
    With sht
        Set rng = .Range(.Cells(1, i), .Cells(.Rows.Count, i).End(xlUp))
        Set lb = sht.ListBoxes.Add(100 * i, 10, 100, 100) 'just an indicative way to create the List boxes without them overlapping
    End With
    lb.Name = "ListBox" & i
    lb.MultiSelect = 2
    For Each cell In rng
        lb.AddItem cell.Value
    Next cell
Next i
End Sub

更新(以覆盖所做的评论)

UPDATE (to cover the comment made)

我更新了上面的代码，将列表框命名为"ListBox1"，"ListBox2"等，而不是"List Box 1"等.

I updated the code above to name the list boxes as "ListBox1" , "ListBox2" etc instead of "List Box 1"etc.

要引用列表框之一，您需要使用对ListBoxes集合的引用.该集合属于列表框所在的工作表.例如，要引用"ListBoxi"，其中i = 1,2 ... n，您需要这样做:

To refer to one of the list boxes you need to use a reference to the collection of ListBoxes. This collection belongs to the sheet where the listboxes are located. For example, to refer to "ListBoxi", where i=1,2...n you need to do it like so:

sht.ListBoxes("ListBox" & i)

不幸的是，我没有已知的.SelectedItems.Count或类似方法，可以与表单控件列表框一起使用.

Unfortunately there is no .SelectedItems.Count or similar method, that I know of, which you can use with a form control List Box.

请记住，例如，您可以找到"ListBox1"的选定项目数，如下所示:

Having that in mind, you can find the number of selected items of "ListBox1" for example, like so:

Dim selectedItems As Long
selectedItems = 0
Set lb = sht.ListBoxes("ListBox" & 1)
For i = 1 To lb.ListCount Step 1
    If lb.Selected(i) Then
        selectedItems = selectedItems + 1
    End If
Next i
If selectedItems = 0 Then
    MsgBox "No user selected"
End If

请记住以下几点:

1. 第一项的索引从0到1，这取决于列表框是否在用户窗体上
2. 要使用名称类似于Listbox1.DoSomething的列表框，它必须是ActiveX控件而不是Form控件.

1. The index of the first item varies from 0 to 1 depending on whether the list box is on a userform or not
2. To refer to a listbox using it's name like Listbox1.DoSomething, it needs to be an ActiveX control and not a Form control.

这篇关于如何解决生成的列表框并在VBA中动态添加项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！