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
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:
0
to 1
depending on whether the list box is on a userform or notListbox1.DoSomething
, it needs to be an ActiveX
control and not a Form control.
Thank you @Stavros ! Generating the Listboxes works now properly, I am now having problems with reading values. It seems I cannot read out the items from those generated ListBoxes which I named lb.name = "ListBox" & i.
If ListBox1.SelectedItems.Count = 0 Then MsgBox "No Item Selected" End If
- Throws the error "Object not found"@DXR I have updated my post to answer to your comment. If this has solved your issues please consider accepting it as an answer.
Hi thank you so much for your help @stavros ! I have two concerns: 1) Why do you need to define the variable "i" as "long". Wouldnt "integer" be enough? 2) Is it possible to programm a "OnClick" event on Form Listboxes? e.g.: Selecting an item in Listbox1 automatically generates a second Listbox with further items.
@DXR you could for example build a
Sub
inModule1
and use the.OnAction
property of the list box. When the listbox is created you can set this property likelb.OnAction = "Module1.runThis"
, whererunThis
is thesub
you want to run whenever the listbox is clicked. For more info I suggest you post a new question