Listbox userform, multiple values into same cell.

[freddie mitchell]

Hello lovely people,

With great help from hero member Fluff, I've got a code that adds values entered into a listbox by a user. This code is perfect at putting multiple values into multiple cells. For example, value X, Y, Z go into cells A1, A2 and A3.

However, can anyone help me change this code so that multiple values can be put into one cell. For example, value X, Y, Z all go into cell A1.
Below is my abortive attempt to modify Fluff's code for entering values into one cell. As it stands, if the user selects either "peanut" or "hazelnut" then they will appear in cell 4. But if the user selects both peanut and hazelnut then only hazelnut will appear in the cell.

Dim h As Long
With Me.allergylist
    For h = 0 To .ListCount - 1
    If .Selected(h) Then
    Select Case .List(h)
                Case "peanuts"
                Cells(r, 4).Value = Cells(r, 5).Value & "peanuts"
                Case "hazelnuts"
                Cells(r, 4).Value = Cells(r, 5).Value & "hazelnuts"
               
End Select
End If
Next h
End With

 

[DanteAmor]

Try this:

  Dim h As Long
  With Me.allergylist
    For h = 0 To .ListCount - 1
      If .Selected(h) Then
        Select Case .List(h)
          Case "peanuts", "hazelnuts"
            Cells(r, 4).Value = Cells(r, 4).Value & .List(h) & " "
        End Select
      End If
    Next h
  End With

 

[freddie mitchell]

Thank you, its just the ticket! Out of curiosity, why did the original modified code not work? Was it overwriting each entry?

 

[DanteAmor]

Cells(r, 4).Value = Cells(r, 5).Value & "peanuts" Case "hazelnuts"

To put the result in the same cell they must have the same column 4 and you had 4 and 5