PDA

View Full Version : [SOLVED] Move selected columns data from UserForms listbox to different ranges in Worksheet



oxicottin
01-24-2013, 05:26 PM
There are 4 columns in my UserForm’s listbox, and If I select a row I needed the value in the selected row for each column to be copied to a worksheet (WorkOrder).

Listbox column 1’s value would be moved to Range("C10:C46")in WorkSheet (WorkOrder)
Listbox column 2’s value would be moved to Range("E3")in WorkSheet (WorkOrder)
Listbox column 3’s value would be moved to Range("C6")in WorkSheet (WorkOrder)
Listbox column 4’s value would be moved to Range(F6")in WorkSheet (WorkOrder)
Is the columns just identified by a column number or how are they identified? And is there a way to just let the user select one row. The listbox is a multi select listbox but for this I need to just allow the user to select only one row. How can I do this? I included a portion of my workbook that shows data ect.

Thanks!

Kenneth Hobs
01-25-2013, 07:11 AM
You should think about what you want after the data is copied. I don't think that your WorkOrder sheet's Activate code will meet your needs. When using the RowSource property for the Listbox control, you need to change the RowSource or remove the rows. It just depends on what you need.

If you only want single item selection in your ListBox, set the MultiSelect property to 0.


Private Sub btnCopyToWorkorder_Click()
Dim lIndex As Integer
With lbxOracleProduct
lIndex = .ListIndex
Sheets("WorkOrder").Range("C10") = .List(lIndex, 0)
Sheets("WorkOrder").Range("E3") = .List(lIndex, 1)
Sheets("WorkOrder").Range("C6") = .List(lIndex, 2)
Sheets("WorkOrder").Range("F6") = .List(lIndex, 3)
End With
End Sub

oxicottin
01-25-2013, 08:29 AM
That worked great for what I needed thanks!

I have the current version and one more question, There is a search on my original UserForm and I cant figure out how to get the results to be at the top of the listbox instead of scattered threw the listbox. What can I add to the VBA to bring the items found to the top of the list? Thanks!

Kenneth Hobs
01-25-2013, 02:07 PM
I like your selection routine.

I guess you could try a Sort for the RowSource. You might need a userform.repaint, I forget.

I posted a wildcard drilldown method some time back at: http://www.ozgrid.com/forum/showthread.php?t=65707