Solved: Selection to particular column and 1st avail row

[thekneeguy]

Hello, this was pasted a few days ago but I dont' think I explained it thoroughly enough so I will try again. I am desperate to fix this as it is the last thing I need to finish this project so any help or direction would be greatly! appreciated.

I downloaded a very smart macro from John Lacher's website. The macro takes the user's selection of a product and its corresponding unit cost and using an index function on the product worksheet, the chosen product is indexed to row 1 column C (autonumber of product in list 1,2,3 ...) D (Product name) and # (product cost). (The macro also clears the entire row if the entry in column b is deleted.....that works great.)

The macro works very well except I need to modify it somewhat to do two things.....

1) At present, the product is placed into any column that the cursor was last left. I need to control user error by forcing it to ONLY place the selection in column B and the unit cost in column C.

and

2) The macro should check for the first available row and enter the succeeding choices into only available rows. As of now it will enter a choice directly on top of one that is already in the row.

Can anyone figure out what I need to do or did wrong to overcome these two obstacles to finishing this project? Thank you so much!!

[VBA] 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

Unload Me
End With
End Sub
[/VBA]

[malik641]

Could you post the workbook thekneeguy?

[thekneeguy]

the workbook or the worksheet that the list box populates?

[Bob Phillips]

Both.

[thekneeguy]

the workbook is way too big to post here. do you have an email i think i can zip it and try to email it

[Bob Phillips]

Can you not break it down to a sample workbook that demonstrates the problem? By posting here, you get a greater chance of a solution.

[thekneeguy]

I understand but the macro is so integral to the overall program that it would be as difficult for me to break it apart as it would be to come up wih a work around which I hae done. I can't remember how we did it last time but I know it worked so I will jus instruct the user that they must put the cursor where they want the entry. Thanks for trying I really appreciate it.

[thekneeguy]

Thanks to all of you that helped wit this. By putting together piece of information from several people and their ideas, we were able to work this out! Thank you very much!