PDA

View Full Version : Refining ListBoxes



sgr
04-28-2010, 11:29 PM
Right now I have ListBox1 that is populated from a worksheet (Sheet1) within the same workbook. I can populate ListBox2 with selections from ListBox1. If I click on the selections in ListBox2, the corresponding row on Sheet1 will be copied and pasted in the next empty row in another workbook. Ultimately, I would like to input data on Sheet1, filter the data on Sheet2 and import rows from Sheet2.

I have two questions:
1. I can get ListBox1 to populate from Sheet2, but when I select the items to copy, the rows are being copied from Sheet1. I can't get everything to populate, find and copy off of Sheet2.
2. Instead of clicking on the items in ListBox2 to initiate the copying of rows into another workbook. Can I click a command button that will copy those selections automatically (instead of clicking on each one)?

What I have so far:
Private Sub CommandButton1_Click()
Dim i As Long, msg As String


With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i)
End If
Next i
End With

If msg = vbNullString Then
'If nothing was selected, tell user and let them try again
MsgBox "Nothing was selected! Please make a selection!"
Exit Sub
Else

ListBox2.AddItem msg

End If
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub ListBox2_Click()
Dim i As Long, msg As String, irow As Integer

Application.ScreenUpdating = False
'select the data and copy to new workbook
With ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i)

Cells.Find(What:=msg, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Select
irow = ActiveCell.Row
Rows(irow).Select
Selection.Copy
Workbooks.Open Filename:="X:\trial database.xls"
'Find last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
'Paste data
Windows("trial database.xls").Activate
ActiveSheet.Paste

'Closes trial database
Windows("trial database.xls").Activate
ActiveWindow.Close True
Application.CutCopyMode = False
End If
Next i

End With
End Sub

Private Sub UserForm_Initialize()

Dim varlist As Variant
Dim lngrow As Long

Lrow = Sheets("Sheet3").Range("A65536").End(xlUp).Row
Plist = Sheets("Sheet3").Range("A1:C" & Lrow)
ListBox1.List = Plist

End Sub

Bob Phillips
04-29-2010, 01:36 AM
I can't see where you copy data re point 1, and aren't you already doing point 2 in your code?

Try posting the workbook(s).

sgr
04-29-2010, 08:02 AM
Thanks, xld.

I've attached the workbook.

Re point 1 - I've tried messing around with it, although it populates from Sheet 2, it still pastes from Sheet 1.

Re point 2 - I would like to click the "upload all" button and have all of the corresponding rows in ListBox2 paste into another workbook. I can click on each individually, but If I have quite a few items in ListBox2 I would like to do them with one click.

Thanks, again.