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,
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
In what way doesn't it work?
 

freddie mitchell

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
So the listbox is set to multi select, correct?
 

freddie mitchell

New Member
Joined
Apr 14, 2011
Messages
33

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
In that case you need to loop through the listbox to see which items have been selected like
VBA Code:
   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
 
Solution

freddie mitchell

New Member
Joined
Apr 14, 2011
Messages
33

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
If you want to use a multiselect listbox, then you have to loop through the values to see which have been selected.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,212
Messages
5,640,895
Members
417,177
Latest member
njosh

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