Extracting selections from multi-select listbox form

[marcjasiq]

Hello,

I've read several posts here and elsewhere trying to find a way to extract selected items from a multi-select listbox form into a column in a separate sheet but have had no luck thus far.

I have the aforementioned listbox (List Box 35 in Sheet 4) with around 100 or so values in it. I need to produce a column in Sheet 1 that populates any selected values from Sheet 4 - List Box 35.

Any help is greatly appreciated.

Thank you.

 

[hotabae]

Here is some sample code

Using ""List Box 35" from Sheet 4, sending to Column A on Sheet 1.

Private Sub listBoxExtract()
    
    Dim Cnt As Long
    
    Dim outputSheet As Worksheet
    Set outputSheet = Sheets("Sheet1")
    
    Dim lbox As ListBox
    Set lbox = Sheets("Sheet4").Shapes("List Box 35").OLEFormat.Object
    
    Dim numSelected
    numSelected = 1
    
       For Cnt = 1 To lbox.ListCount - 1
          If lbox.Selected(Cnt) Then
		  
	     'May need to Modify The values in "Cells()" to designate destination
             outputSheet.Cells(numSelected, 1).value = lbox.List(Cnt)
			 
             numSelected = numSelected + 1
			 
          End If
       Next Cnt

End Sub

 

[marcjasiq]

Thank you!!! That worked. Is there anything additional to add in there in order for the extract in Sheet 1 to refresh if something is unselected? I assigned the macro to a button in Sheet 1 and upon clicking, it extracts all selections into Column A -- however, if I go back to my list box and unselect something, it will remain in my Column A export even upon re-clicking the macro-assigned button. New selections will get picked up, but something unselected is not removed.

 

[marcjasiq]

Actually I can just clear column A and click the Macro-assigned button to completely refresh the list which captures anything that was unselected.

My only other question is what if I have several list boxes and want List Box 35 in Column A, List Box 36 in Column B, etc?

Thank you again!

 

[hotabae]

Add this code to a module:

Sub listBoxExtract()
    
    Dim Cnt As Long
    
    Dim outputSheet As Worksheet
    Set outputSheet = Sheets("Sheet1")
    
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim lbox As ListBox
    Set lbox = ws.Shapes(Application.Caller).OLEFormat.Object
    
    Dim numSelected As Long
    numSelected = 1
    
    Dim outCol As Long
    
    Select Case lbox.Name
    
        Case "List Box 35"
            outCol = 1
        
        Case "List Box 36"
            outCol = 2
        
    End Select
    
    outputSheet.Columns(outCol).ClearContents
    
       For Cnt = 1 To lbox.ListCount
          If lbox.Selected(Cnt) Then
          
         'May need to Modify The values in "Cells()" to designate destination
             outputSheet.Cells(numSelected, outCol).Value = lbox.List(Cnt)
             
             numSelected = numSelected + 1
             
          End If
       Next Cnt
       
End Sub

After you've added it, Right click any applicable ListBox and "Assign Macro..."

Select listBoxExtract, and then any time the list box updates, the changes will appear on Sheet 1.

I've designated 35 to go to the first column, and 36 to go to the second column. you can add/modify as needed.

Edit: "for" loop needed to run one more time (should be accurate now)