ListBox Issues

[josephw34789]

http://i1292.photobucket.com/albums/...ps162ce1b4.png

The data entered into the form will go in columns M:S as follows:

Cost

Condition

Trace

Tag Info

Lead Time

Contact

Comments

<tbody>
</tbody>

Hello,
I was hoping to get some help on a project--but It is more of just a single question regarding a project that I have mostly completed. I could not find anything that would help me in the forums. I have attached a sample document. Here is how this works:


Reports are generated and imported into excel (The attached document is one of these reports. They are always in the same format; however they vary in length). A salesperson decides who is and is not worth calling. They call the companies that they feel are "worth it" and get quotes on the part number listed with that company.


I designed a user-form that pops up and allows the user to click the company, part number, and condition of the part for which they are getting a quote. It then allows the user to enter all of the quote information that they hear while on the phone. (Price, lead time, contact name, etc).


As of now, the list boxes are populated with all the companies that appear on the spreadsheet, without duplicates. This was great at first. But now I want the listbox to only populate with companies that are in rows that are highlighted by the user. That way, the user doesn't have to scroll through the exhaustive list of companies. (The example attached is not an exhaustive list; but they usually are). They can look through the report, highlight the rows that contain the companies they feel are worth calling, THEN initiate the userform, so that the listbox of companies only consists of companies that are highlighted.

Here is the code that populates the listboxes:

Code:

'COMPANIES
Set rnArea = ActiveSheet.Range(Range("B5"), Range("B65536").End(xlUp))


On Error Resume Next
For Each rnCell In rnArea
List.Add rnCell.Value, CStr(rnCell.Value)
Next rnCell
On Error GoTo 0


For Each vaValues In List
NewUserForm.lstcompanies.AddItem vaValues
Next


Dim List1 As New Collection
Dim rnArea1 As Range, rnCell1 As Range
Dim vaValues1 As Variant


'PART NUMBERS
Set rnArea1 = ActiveSheet.Range(Range("D5"), Range("D65536").End(xlUp))


On Error Resume Next
For Each rnCell1 In rnArea1
List1.Add rnCell1.Value, CStr(rnCell1.Value)
Next rnCell1
On Error GoTo 0


For Each vaValues1 In List1
NewUserForm.lstParts.AddItem vaValues1
Next


'CONDITIONS
Dim List2 As New Collection
Dim rnArea2 As Range, rnCell2 As Range
Dim vaValues2 As Variant


Set rnArea2 = ActiveSheet.Range(Range("H5"), Range("H65536").End(xlUp))


On Error Resume Next
For Each rnCell2 In rnArea2
List2.Add rnCell2.Value, CStr(rnCell2.Value)
Next rnCell2
On Error GoTo 0


For Each vaValues2 In List2
NewUserForm.lstconds.AddItem vaValues2
Next




NewUserForm.Show


End Sub

What do you think?