Form List box autopopulates doubles entry and wrong columns

[thekneeguy]

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

[OBP]

Sholdn't you be selecting the column that you want to put the data in just prior to
"With Activesheet"?
I also do not see any counters counting the number of rows to enter or a copy/paste action that pts al of the data in he correct place.

[Bob Phillips]

A couple of thoughts


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
Dim iLastRow As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
.Cells(iLastRow, 2).Value = Worksheets("HipProducts").Range("D1")
.Cells(iLastRow, 3).Value = Worksheets("HipProducts").Range("E1")
Unload Me
End With
End Sub

[thekneeguy]

Thank you I tried that myself, actually. However, the macro which makes the selection link work requires the

Selection.Cells(1) = Worksheets("HipProducts").Range("D1")

Selection.Cells(1).Offset(0, 1) = Worksheets("HipProducts").Range("E1")

Without this, the selection box does not populate anything. So, I added this back to yours and tried it with the range definition that you suggested. It worked, however it was the same thing. First entry was doubled and the rest were populated anywhere the cursor was last. I have used this program once before several years ago and I didnt seem to have this problem. Do you think I need some sort of "if listindex.value =" statement? THank you for trying to help me with this.