Listbox userform

[freddie mitchell]

Hello everyone.

Super simple question that, I assume, has a blindingly obvious answer that I'm missing.

I have a ComboBox on my userform that populates select cells in a connected sheet. Feedback from the userform's users means that I'm now transforming the ComboBox into a listbox. I've got the listbox working in the userform, except it won't populate the cells. I've tried modifying the existing combobox code, see below, but no such luck. Does anyone have thoughts / suggestions on how to solve this?



Existing combobox:

Public Sub Insert_Row()
If ComboBox.Value = "X1" Then Cells(r, 9).Value = Cells(r, 10).Value & "X1"
If ComboBox.Value = "X2" Then Cells(r, 10).Value = Cells(r, 11).Value & "X2"
If ComboBox.Value = "X4" Then Cells(r, 8).Value = Cells(r, 9).Value & "X4"
If ComboBox.Value = "X5" Then Cells(r, 11).Value = Cells(r, 12).Value & "X5"
If ComboBox.Value = "X6" Then Cells(r, 12).Value = Cells(r, 13).Value & "X6"

    End With

End Sub

And here's my basic changes:

If ListBox.Value = "X1" Then Cells(r, 9).Value = Cells(r, 10).Value & "X1"
If ListBox.Value = "X2" Then Cells(r, 10).Value = Cells(r, 11).Value & "X2"
If ListBox.Value = "X4" Then Cells(r, 8).Value = Cells(r, 9).Value & "X4"
If ListBox.Value = "X5" Then Cells(r, 11).Value = Cells(r, 12).Value & "X5"
If ListBox.Value = "X6" Then Cells(r, 12).Value = Cells(r, 13).Value & "X6"

Thank you,

 

[Fluff]

In what way doesn't it work?

 

[freddie mitchell]

hello Fluff,

Currently the listbox does not add the selected value to the relevant cell.
So, if working correctly, a user should be able to select X1 and X2 from the listbox, which will then be entered into the relevant sections of the sheet (in this case r, 9 and r, 10 respectively) when the userform's okbutton is clicked.
Unfortunately this is not happening, a user can click on X1 and X2, press the okbutton, but no value appears in the sheet cells.

hope that makes sense and thank you for your help,

 

[Fluff]

So the listbox is set to multi select, correct?

 

[freddie mitchell]

Yes the listbox is set to multi-select. (and this part works, so a user can select multiple options in the listbox)

 

[Fluff]

In that case you need to loop through the listbox to see which items have been selected like

   Dim i As Long
  
   With Me.ListBox1
      For i = 0 To .ListCount - 1
         If .Selected(i) Then
            Select Case .List(i)
               Case "X1"
                  Cells(r, 9).Value = Cells(r, 10).Value & "X1"
               Case "X2"
                  Cells(r, 10).Value = Cells(r, 11).Value & "X2"
            End Select
         End If
      Next i
   End With

 

[freddie mitchell]

Hello,

Thank you, I'll try this now. Just to check, is there not a simpler way to solve this? I assumed that Listbox would have a code to add its values to a worksheet?

 

[Fluff]

If you want to use a multiselect listbox, then you have to loop through the values to see which have been selected.

 

[freddie mitchell]

Hi Fluff,

Its working and your explanation makes perfect sense, thank you for explaining it!

 

[Fluff]

You're welcome & thanks for the feedback.