thekneeguy
10-06-2005, 03:18 PM
I have a user form with a list box that the user chooses and item. The OK commandbutton then autopopulates the sheet1 with the item choice in column (preferably b) and its corresponding cost in a column (to be C). For the next choice, the first available row is found. The code is below.
Here is my problem,
1) it finds the next available row however it finds it in any column that the cursor happens to be in i.e., if the user left the cursor in column g it will place the item choise and cost in g and h instead of b and c columns.
2) For some reason, the first item chosen (only the first) enters two entries of the same thing in the first row and the next row.????
Does anyone know where I messed up the code? I really appreciate any help or direction. Thank you
Private Sub CommandButton1_Click()
'if the listindex of listbox equals -1 ... nothing selected
If lstSelection.ListIndex = -1 Then
MsgBox "No item selected", vbExclamation
Exit Sub
End If
Range("SelectionLink") = lstSelection.ListIndex + 1
Selection.Cells(1) = Worksheets("HipProducts").Range("D1")
Selection.Cells(1).Offset(0, 1) = Worksheets("HipProducts").Range("E1")
Dim iLastRow As Long
With ActiveSheet
iLastRow = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Cells(iLastRow, 2).Value = Selection.Cells(1)
Cells(iLastRow, 3).Value = Selection.Cells(1).Offset(0, 1)
Unload Me
End With
End Sub
Here is my problem,
1) it finds the next available row however it finds it in any column that the cursor happens to be in i.e., if the user left the cursor in column g it will place the item choise and cost in g and h instead of b and c columns.
2) For some reason, the first item chosen (only the first) enters two entries of the same thing in the first row and the next row.????
Does anyone know where I messed up the code? I really appreciate any help or direction. Thank you
Private Sub CommandButton1_Click()
'if the listindex of listbox equals -1 ... nothing selected
If lstSelection.ListIndex = -1 Then
MsgBox "No item selected", vbExclamation
Exit Sub
End If
Range("SelectionLink") = lstSelection.ListIndex + 1
Selection.Cells(1) = Worksheets("HipProducts").Range("D1")
Selection.Cells(1).Offset(0, 1) = Worksheets("HipProducts").Range("E1")
Dim iLastRow As Long
With ActiveSheet
iLastRow = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Cells(iLastRow, 2).Value = Selection.Cells(1)
Cells(iLastRow, 3).Value = Selection.Cells(1).Offset(0, 1)
Unload Me
End With
End Sub