PDA

View Full Version : Solved: Listbox populated by Userform selections



tccmdr
05-23-2007, 12:01 AM
Your assistance is greatly appreciated:help ,

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!:banghead: !


Mike

mvidas
05-23-2007, 06:49 AM
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
05-23-2007, 06:55 AM
Ok, just a quick example of the methods you could use: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

tccmdr
05-23-2007, 04:33 PM
Appreciate your assistance on this matt.
I'll try the piece of code and let you know how it goes.........

tccmdr
05-23-2007, 04:53 PM
Matt,

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

mvidas
05-24-2007, 06:53 AM
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: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 SubIf 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):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


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):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 SubLet me know if you get stuck on any other part of this!
Matt

tccmdr
05-24-2007, 02:23 PM
Matt,

Thanks for the help so far....

Because I'm modifying one of Aaron's creations I thought it best I leave his reference.:dunnoI've used the code and it works fine

Thanks again, no doubt I'll be back:friends: