Forum Discussion
ehowarth
May 06, 2022Copper Contributor
Concatenate values from a listbox into a cell
Hi Everyone, I have a problem that I have tried every solution I can think of to solve. I have a Listbox that contains multiple contract codes (A029, A031, A032, etc...) that is set to multi-...
HansVogelaar
May 06, 2022MVP
Create a macro in a standard module (the kind you create by selecting Insert > Module in the Visual Basic Editor):
Sub ListValues()
Dim lbx As ListBox
Dim i As Long
Dim s As String
Set lbx = Worksheets("ListBox").ListBoxes(Application.Caller)
For i = 1 To lbx.ListCount
If lbx.Selected(i) Then
s = s & ", " & lbx.List(i)
End If
Next i
If s <> "" Then
s = Mid(s, 3)
End If
Worksheets("Output").Range("C7").Value = s
End Sub
Assign this macro to the list box.
ehowarth
May 06, 2022Copper Contributor
Thank you! This works almost perfectly and is similar to one of the codes I tried before. My only issue is that I can't have a space after the comma separating the concatenated values. When I remove the space from the code, it cuts off the first digit of the first concatenated value.
If lbx.Selected(i) Then
s = s & "," & lbx.List(i)
For instance if I select A080 and A092 the value in C7 would read 080,A092
If lbx.Selected(i) Then
s = s & "," & lbx.List(i)
For instance if I select A080 and A092 the value in C7 would read 080,A092
- HansVogelaarMay 06, 2022MVP