How to address generated ListBoxes and add Items dynamically in VBA?

Question

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?

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

Answer 1

Use the .ControlFormat.ListFillRange instead, and set MultiSelect to 3. Something like this should work for you:

Sub tgr()

    Dim FS As Worksheet
    Dim lb As Shape
    Dim i As Long

    Set FS = ActiveWorkbook.Worksheets("FS")
    For i = FS.Columns("A").Column To FS.Cells(1, FS.Columns.Count).End(xlToLeft).Column
        Set lb = FS.Shapes.AddFormControl(xlListBox, (i - 1) * 100 + 10, 10, 100, 100)
        With lb
            .ControlFormat.MultiSelect = 3
            .ControlFormat.ListFillRange = FS.Range(FS.Cells(1, i), FS.Cells(FS.Rows.Count, i).End(xlUp)).Address(External:=True)
        End With
    Next i

End Sub

Answer 2

I suggest you do it like so:

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)

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

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)

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

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

A few things to keep in mind:

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.