VBA List Box Populate Row with all Selections

radished

New Member
Joined
May 24, 2017
Messages
5
I have a ListBox where you can select multiple values, and the values are populated in the called out cell when a button is pressed. What I need help with is getting the values to populate the row in successive cells.
So if I select Cust1, Cust2, and Cust3 I need them to populate in L3, M3, N3. If 4 Customers were selected L3, M3, N3, O3, etc. Here is the VBA for the List Box.


Sub Rectangle1_Click()

Dim xSelShp As Shape, xSelLst As Variant, I As Integer
Set xSelShp = ActiveSheet.Shapes(Application.Caller)
Set xLstBox = ActiveSheet.ListBox1
If xLstBox.Visible = False Then
xLstBox.Visible = True
xSelShp.TextFrame2.TextRange.Characters.Text = "Pickup Options"
Else
xLstBox.Visible = True
xSelShp.TextFrame2.TextRange.Characters.Text = "Select Options"
For I = xLstBox.ListCount - 1 To 0 Step -1
If xLstBox.Selected(I) = True Then
xSelLst = xLstBox.List(I) & xSelLst
End If
Next I
If xSelLst <> "" Then
Range("L3:N3") = Mid(xSelLst, 1, Len(xSelLst))
Else
Range("L3") = "N/A"
End If
End If
End Sub

Can I achieve this with how its currently written?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,214,376
Messages
6,119,180
Members
448,871
Latest member
hengshankouniuniu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top