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,

 

[freddie mitchell]

One last question,

The listbox works great when only one option is selected. However, when a user selects two values that appear in adjoining columns, then the values sometimes get inputted incorrectly.

I've put a screen shot below. In the screenshot: for the first row the user selected X3 and X4 from the listbox, the next row they selected only X3, then the final row they selected only X4.

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

What's even stranger, however, is that there is no issue for X5 and X6. See below the code and the relevant screenshot (in which the user selected X5 for the first row, X6 for the second and X5 and X6 for the third).

Case "X5"
                  Cells(r, 10).Value = Cells(r, 11).Value & "X5"
                Case "X6"
                  Cells(r, 11).Value = Cells(r, 12).Value & "X6"

If you have any clues to why this is that would be amazing!

thank you,

 

[Fluff]

Without knowing the exact code, what is already in the cells & what you are trying to do, there is not much I can suggest.

 

[freddie mitchell]

Without knowing the exact code, what is already in the cells & what you are trying to do, there is not much I can suggest.

Yes, I slightly realised the futility of my question when I posted it! I'll keep tinkering until it works (probably its a formatting issue)

 

[Fluff]

Looking at your original code you have

If ListBox.Value = "X1" Then Cells(r, 9).Value = Cells(r, 10).Value & "X1"
If ListBox.Value = "X4" Then Cells(r, 8).Value = Cells(r, 9).Value & "X4"

If the user select X1 & X4 col 9 would end with X1 & then col 8 takes that & adds X4 to the end. Not sure if that's what you intended.