Solved: Listbox populated by Userform selections

[tccmdr]

Your assistance is greatly appreciated ,

I have a userform containing three selection comboboxes and a command button to post selections to the userform listbox, a listbox to hold the posted selections and a command button to populate my worksheet with the list .

I cannot work out the code to continually populate the listbox with the posted selections made, then pass the entire list to my worksheet! !


Mike

[mvidas]

Hi Mike,

Would you be able to post a workbook with the userform you have so far, or if your workbook would have sensitive information then just the code you have so far?

Without that, all we can really do is give you a guide.. but if you'd like that give me a couple minutes I can write up a quick example of one way you could do it

Matt

[mvidas]

Ok, just a quick example of the methods you could use:[vba]Private Sub CmdBtnComboBoxStuffToListbox_Click()
ListBox1.AddItem ComboBox1.Value
ListBox1.AddItem ComboBox2.Value
ListBox1.AddItem ComboBox3.Value
End Sub
Private Sub CmdBtnListboxToSheet_Click()
Range("A1").Resize(ListBox1.ListCount, 1).Value = ListBox1.List
Unload Me
End Sub[/vba]

[tccmdr]

Appreciate your assistance on this matt.
I'll try the piece of code and let you know how it goes.........

[tccmdr]

Matt,

I've attached an example workbook in order to avoid any info breach

[mvidas]

Interesting that Aaron Blood is the author of your workbook

What you're going to want to do is change the ColumnCount property of your listbox to 3 (since you have 3 comboboxes that you want added as one record). As an FYI, listbox lists have a base of 0, so columns 1,2,3 would be 0,1,2 to VBA (should help for future edits).

To add the entries to the listbox, you'll need something like:[vba]Private Sub CommandButton1_Click()
ListBox1.AddItem ComboBox1.Value
ListBox1.List(ListBox1.ListCount - 1, 1) = ComboBox2.Value
ListBox1.List(ListBox1.ListCount - 1, 2) = ComboBox3.Value
End Sub[/vba]If it will help you realize what is going on, you can separate my line 1 into 2 parts. AddItem adds a new row to the listbox, so in reality what I'm doing is (this also works):[vba]Private Sub CommandButton1_Click()
ListBox1.AddItem
ListBox1.List(ListBox1.ListCount - 1, 0) = ComboBox1.Value
ListBox1.List(ListBox1.ListCount - 1, 1) = ComboBox2.Value
ListBox1.List(ListBox1.ListCount - 1, 2) = ComboBox3.Value
End Sub[/vba]


Next, you'll want to move the list to the worksheet (on the Front_End sheet, starting on the next available row in column D it seems):[vba]Private Sub CommandButton2_Click()
Sheets("Front_End").Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Resize( _
ListBox1.ListCount, ListBox1.ColumnCount).Value = ListBox1.List
Unload Me
End Sub[/vba]Let me know if you get stuck on any other part of this!
Matt

[tccmdr]

Matt,

Thanks for the help so far....

• Because I'm modifying one of Aaron's creations I thought it best I leave his reference.

I've used the code and it works fine

Thanks again, no doubt I'll be back