Forum Discussion

ehowarth's avatar
ehowarth
Copper Contributor
May 06, 2022

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-select. I need the selected values from the listbox to concatenate into a specific cell (C7 on the Output tab) seperated by /'s. I then need to clear the selections from the list box and the value of C7 when the CLEAR FORM button is selected.

 

Example:

If the User selects A029, A032, and A080 the value in C7 would read A029/A032/A080, or

If the User selects A063 and A114 the value in C7 would read A063/A114

 

 

Any thoughts on how to accomplish this?

excel
Macros and VBA

3 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 06, 2022

    ehowarth 

    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's avatar
      ehowarth
      Copper Contributor
      May 06, 2022
      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

Resources