sgr
05-13-2010, 09:36 PM
I am in a bind. Please help.
I have a workbook in which Sheet 1 is populated by the user. Sheet 2 reorganizes the data (user doesn't touch or see Sheet 2). The user clicks a command button that will ultimately upload data from Sheet 2 into another workbook.
When the user clicks the command button, a user form loads.
1. ListBox 1 contains a list of items from the first column of Sheet2 -Good.
2. Items can be added from ListBox1 to ListBox2 - Good.
3. The row (from Sheet2) corresponding to the item in ListBox2 is copied and pasted in another workbook when I click the item - Sorta Good. The row that is copied and pasted is the row that happens to be highlighted in Sheet1:banghead: . I tried to acivate Sheet2, but then I get a End IF without Block If error.
4. I would like to add all corresponding rows that are listed in ListBox2 with the "Upload ALL" command button, but you can forget about it if I can't get past #3.
Any help would be greatly appreciated. Code is below.
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 CommandButton3_Click()
End Sub
Private Sub ListBox1_Click()
End Sub
Private Sub ListBox2_Click()
Dim i As Long, msg As String, irow As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
Application.ScreenUpdating = False
'select the data and copy to new worksheet
With ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i)
With ws
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:="K:\qc database\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
End If
Next i
'Closes trial database
Windows("trial database.xls").Activate
ActiveWindow.Close True
Application.CutCopyMode = False
End With
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
' populates from Sheet2
Dim varlist As Variant
Dim lngrow As Long
Lrow = Sheets("Sheet2").Range("A65536").End(xlUp).Row
Plist = Sheets("Sheet2").Range("A2:C" & Lrow)
ListBox1.List = Plist
End Sub
I have a workbook in which Sheet 1 is populated by the user. Sheet 2 reorganizes the data (user doesn't touch or see Sheet 2). The user clicks a command button that will ultimately upload data from Sheet 2 into another workbook.
When the user clicks the command button, a user form loads.
1. ListBox 1 contains a list of items from the first column of Sheet2 -Good.
2. Items can be added from ListBox1 to ListBox2 - Good.
3. The row (from Sheet2) corresponding to the item in ListBox2 is copied and pasted in another workbook when I click the item - Sorta Good. The row that is copied and pasted is the row that happens to be highlighted in Sheet1:banghead: . I tried to acivate Sheet2, but then I get a End IF without Block If error.
4. I would like to add all corresponding rows that are listed in ListBox2 with the "Upload ALL" command button, but you can forget about it if I can't get past #3.
Any help would be greatly appreciated. Code is below.
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 CommandButton3_Click()
End Sub
Private Sub ListBox1_Click()
End Sub
Private Sub ListBox2_Click()
Dim i As Long, msg As String, irow As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
Application.ScreenUpdating = False
'select the data and copy to new worksheet
With ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i)
With ws
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:="K:\qc database\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
End If
Next i
'Closes trial database
Windows("trial database.xls").Activate
ActiveWindow.Close True
Application.CutCopyMode = False
End With
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
' populates from Sheet2
Dim varlist As Variant
Dim lngrow As Long
Lrow = Sheets("Sheet2").Range("A65536").End(xlUp).Row
Plist = Sheets("Sheet2").Range("A2:C" & Lrow)
ListBox1.List = Plist
End Sub