2 Listbox selection to a worksheet

[TonySondergeld]

Hi,
I have two multi column Listbox on a userform, the first listbox1 finds details about a customer Order. Listbox2 displays the current stock. Now Listbox1 is a single line selection and Listbox2 is a multi line selection, So to fill a order you select the order from Listbox1 and then the required stock from listbox2 (which could be 1 or up to 22 selection. Now the part I'm stuck on, with a commandbutton chick to add the selections form each listbox to the All Order worksheet in the next empty row or rows depending on the number of stock selections (May have to loop listbox1 toplace it values with all selection in listbox2), Now as some info in each listbox is the same i only required to place some values from each listbox.
Listbox1 required Values: Column2 to ws.column C, Column3 to ws.column E, Column4 to ws.column D
Listbox2 required values: Column1 to ws.column F, Column3 to ws.column G, Column4 to ws.column H, Column5 to ws.column I, Column6 to ws.column J,

Hope this is enough info, if not let me know
thankyou

 

[AlphaFrog]

This is not tested.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
[color=green]'Listbox1 required Values: Column2 to ws.column C, Column3 to ws.column E, Column4 to ws.column D[/color]
[color=green]'Listbox2 required values: Column1 to ws.column F, Column3 to ws.column G, Column4 to ws.column H, Column5 to ws.column I, Column6 to ws.column J,[/color]
    
[color=green]'Columns numbers are zero based for listboxes. So the 1st column is column #0[/color]
[color=green]'I assumed you mean "Column2" is to be the 2nd column and not column #2 which is the 3rd column[/color]
    
    [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], lb1Index [color=darkblue]As[/color] [color=darkblue]Long[/color], NextRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] ws = Sheets("All Order")
    NextRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    [color=darkblue]If[/color] Me.ListBox1.ListIndex = -1 [color=darkblue]Then[/color]
        MsgBox "Missing selection in Listbox1", vbExclamation, "Invalid Selection"
        [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]Else[/color]
        lb1Index = Me.ListBox1.ListIndex
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]For[/color] i = 0 [color=darkblue]To[/color] Me.ListBox2.ListCount - 1
        [color=darkblue]If[/color] Me.ListBox2.Selected(i) [color=darkblue]Then[/color]
            
            ws.Range("C" & NextRow).Value = Me.ListBox1.List(lb1Index, 1)
            ws.Range("E" & NextRow).Value = Me.ListBox1.List(lb1Index, 2)
            ws.Range("D" & NextRow).Value = Me.ListBox1.List(lb1Index, 3)
            
            ws.Range("F" & NextRow).Value = Me.ListBox2.List(i, 0)
            ws.Range("G" & NextRow).Value = Me.ListBox2.List(i, 2)
            ws.Range("H" & NextRow).Value = Me.ListBox2.List(i, 3)
            ws.Range("I" & NextRow).Value = Me.ListBox2.List(i, 4)
            ws.Range("j" & NextRow).Value = Me.ListBox2.List(i, 5)
            
            NextRow = [color=darkblue]Next[/color]Row + 1
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    Next i
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[TonySondergeld]

Thanks for your help Alpha Frog It works great, Did make a couple small changes. Now the next problem, is there a way that I can adjust this code so that I can at the same time this code is run it adjust 2 worksheets by using the listbox 1 and 2 column selection data to find the original info and change one column in each..
What has to happen.......
Have Listbox1 Column 3 look at Sheet1 column I and when it finds a match change the cell on the same row in column M to say "Completed"
Have Listbox2 Column 3 and column 4 look at Sheet2 column G and H and when it finds a match change the cell on the same row in column K to the Order no In Listbox1 column3, now depending on the number on the number of selection made this may have to loop i think will it?
Again thank you