Curious Results from DblClick Event running on a ListBox in a Userform

[notitia]

I have a userform with a listbox (shown below) that is populated from a spreadsheet (cc_output) generated by an advanced filter.

I am trying to write some code so that when you double click on a record in the listbox a lookup is performed on the spreadsheet (cc_output) to find the matching record and populate all of the data in some comboboxes in the userform.

I am trying to look up using the cc_id which is in the first column of the listbox and which is stored in column A of the spreadsheet cc_output.

This is working perfectly for records with cc_id 17 to 8 in the listbox but when I click on any records with cc_id 7 - 1 they return the wrong record from the spreadsheet.

That is

Double clicking cc_id 17 returns cc_id 17
Double clicking cc_id 16 returns cc_id 16

but...

Double clicking cc_id 7 returns cc_id 17
Double clicking cc_id 6 returns cc_id 16

and so on... until...

Double clicking cc_id 1 returns cc_id 17 also!


ListBox.PNG

Here is an excerpt of my code:

Please Login or Register  to view this content.

Any ideas?

[bakerman2]

Did you already try this ?

Please Login or Register  to view this content.

[Norie]

Have you tried using Application.Match to find the row in A that the id is in?

You could then use that to populate the other controls.

[notitia]

Sincere thanks! This appears to have solved the problem.

I'm not sure I fully understand why though... What was happening during the lookup before that is not happening now?

[notitia]

I added: LookAt:=xlWhole to Find() and this all works now.

[bakerman2]

xlWhole means the text you are searching for must fill the whole cell; in other words, all the text in the cell must be the same as the text you are searching for.

xlPart means the text you are searching for only needs to be part of the text in the cell... there can be additional text in the cell along with the text you are searching for.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.