Extract Multiple Selection Form Control Data

[Hartog]

Hi - I’ve got a worksheet with multiple ListBox form controls (not ActiveX) that I need to extract the selections/inputs from.

There are 6 different listboxes all with multiple selection and about 40x of each. They are aligned by row, e.g customer 1 in in row 1 has listbox 1 in column A, listbox 2 in column B, listbox 3 in column C and so on.. this is repeated for customer 2 in row 2, customer 3 in row 3 and so on..

The perfect solution would delimit the multiple inputs for each listbox with a comma, but I could potentially work around this.

I’ve got some experience with VBA and have been able to extract the inputs of a single listbox (not delimited in a single cell however), but have been unable to loop through all of the listboxes and get the rest of the selection data.

The code I’ve used extracts from List Box 1660 into Sheet 1 C4:

Dim lb As Object
Dim i As Long
Dim c As Integer
Dim col As Integer
Dim Ws As Worksheet
Set Ws = Sheets("ReportingInventory")
Set lb = Worksheets("ReportingInventory").Shapes("List Box 1660")
If lb.Type = msoFormControl Then
If lb.FormControlType = xlListBox Then
For i = 1 To lb.OLEFormat.Object.ListCount
If lb.OLEFormat.Object.Selected(i) Then
c = c + 1
Sheets("Sheet1").Activate
ActiveSheet.Cells(c + 1, 4) = lb.OLEFormat.Object.List(i)
End If
Next i
End If
End If

End Sub

Any help would be appreciated!

 

[Norie]

How are the listboxes named?

PS Here's code to get a comma-separated list of the selected items in the listbox.

Dim Ws As Worksheet
Dim lb As Object
Dim i As Long
Dim cnt As Long
Dim col As Long
Dim arrSelected As Variant

    Set Ws = Sheets("ReportingInventory")
    Set lb = Worksheets("ReportingInventory").Shapes("List Box 1660")
    
    If lb.Type = msoFormControl Then
        If lb.FormControlType = xlListBox Then
        
            Set lb = lb.OLEFormat.Object
            
            ReDim arrSelected(1 To lb.ListCount)
            For i = 1 To lb.ListCount
                If lb.Selected(i) Then
                    cnt = cnt + 1
                    arrSelected(cnt) = lb.List(i)
                End If
            Next i
        End If
        
    End If

    ReDim Preserve arrSelected(1 To cnt)
    
    arrSelected = Join(arrSelected, ",")
        
    MsgBox arrSelected

 

[Hartog]

Thanks Norie - the listboxes are named a bit sporadically at the moment as the file had them that way, e.g column A might be “List Box 1660” in row 1, “List Box 1659” in row 2, “List Box 1658” in row 3 and so on down the ~40 rows. However, this may be similar for column B but start at “List Box 700”.

I’m able to rename (even manually) if required

 

[Norie]

You might be able to loop through all the listboxes and identify their position, i.e. row and column, using the TopLeftCell property.

If, as your first post implies, they are placed in a pattern you should then be able to identify which listboxes belong to which customer.

 

[Hartog]

Yep definitely in a pattern, I’ll investigate the TopLeftCell property.

 

[Hartog]

How are the listboxes named?

PS Here's code to get a comma-separated list of the selected items in the listbox.

Dim Ws As Worksheet
Dim lb As Object
Dim i As Long
Dim cnt As Long
Dim col As Long
Dim arrSelected As Variant

    Set Ws = Sheets("ReportingInventory")
    Set lb = Worksheets("ReportingInventory").Shapes("List Box 1660")
    
    If lb.Type = msoFormControl Then
        If lb.FormControlType = xlListBox Then
        
            Set lb = lb.OLEFormat.Object
            
            ReDim arrSelected(1 To lb.ListCount)
            For i = 1 To lb.ListCount
                If lb.Selected(i) Then
                    cnt = cnt + 1
                    arrSelected(cnt) = lb.List(i)
                End If
            Next i
        End If
        
    End If

    ReDim Preserve arrSelected(1 To cnt)
    
    arrSelected = Join(arrSelected, ",")
        
    MsgBox arrSelected

Having tested this code the output is delimited but it's shown in a message box, as opposed to in a cell on an "output sheet"
Any suggestions?

Thanks

 

[Norie]

The code creates an array, arrSelected, with the selected items from the listbox.

You can do whatever you want with that array.

In the code I posted I used a message box to show the result as a comma separated list.

If you wanted that list in a cell you could use something like this.

Range("A1").Value = Join(arrSelected, ",")

You could also use code to populate multiple cells with the selected items from the listbox.

Really depends what you want to do with the data.