Listbox userform

freddie mitchell

New Member
Joined
Apr 14, 2011
Messages
33
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:

VBA Code:
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:
VBA Code:
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

New Member
Joined
Apr 14, 2011
Messages
33
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.


VBA Code:
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


Screenshot 2021-02-10 115251.png


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).

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

Screenshot 2021-02-10 115906.png



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

thank you,
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 14, 2011
Messages
33
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
Looking at your original code you have
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,539
Messages
5,636,903
Members
416,949
Latest member
propertyscout

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top