How to retrieve multiple values from multiple list boxes by macro?

[bullabulla]

I have a sheet with 8 different listboxes which I have built by using the "View - toolbars - form - listbox " menu in the upper left corner. I have them placed after each other to the left in the sheet.

In one of the listboxes, for example, I have set the "input range" to 10 different countries lined up in cell M1-M10.

"Selection type" is set to multi meaning that the user can choose several different options in the listbox.

In the other 7 listboxes I have the input ranges placed in column N, O, P, Q, R, S, T. The length (number of rows) that the input ranges take up varies from listbox to listbox. All listboxes are multi selection types.

Now assume that a user makes his choices and selects some options in each of these boxes 8 boxes...

I need a macro that loops through all these different listboxes (no matter how many they are because I will probably have to add some more soon) and writes the indexes (or actual values) in the G-column when the code finds an option which has been selected by the user.

Hope you can help me out and let me know if something is unclear.

 

[MickG]

Hi, Try this:- For "Forms" listboxes.

Sub Multi()
Dim s As Shape, Mul As Integer, c As Integer
Columns("G").ClearContents
For Each s In ActiveSheet.Shapes
    
 If s.Type = msoFormControl Then
     If s.FormControlType = xlListBox Then
          For Mul = 1 To s.OLEFormat.Object.ListCount
            If s.OLEFormat.Object.Selected(Mul) Then
                 c = c + 1
                Cells(c, "G") = s.OLEFormat.Object.List(Mul)
             End If
          Next Mul
    End If
 End If
Next s

End Sub

Regards Mick

 

[bullabulla]

Thanks a million Mick

I don't understand much of the code but the important thing for me is that it works... which it does... like a dream

 

[bullabulla]

Btw, would it be complicated to modify the code so the chosen values from the first listbox is displayed in column G, the values of the second listbox in column H, 3rd listbox in column I and so on....?

Thanks again for your help until now.

 

[MickG]

Hi, Try this:-

Sub Multi()
Dim s As Shape, Mul As Integer, c As Integer, col As Integer
col = 6
For Each s In ActiveSheet.Shapes
    
 If s.Type = msoFormControl Then
     If s.FormControlType = xlListBox Then
          c = 0
          col = col + 1
          Columns(col).ClearContents
          For Mul = 1 To s.OLEFormat.Object.ListCount
            If s.OLEFormat.Object.Selected(Mul) Then
                 c = c + 1
                Cells(c, col) = s.OLEFormat.Object.List(Mul)
             End If
          Next Mul
    End If
 End If
Next s

End Sub

Regards Mick