Annoying ListBox Issue with Click event

Hi,

I hope someone may be able to help me with a minor but very annoying issue with the userform listbox click event. I'll try to make the question as comprehensive as possible.

I have a userForm with a textBox and ListBox. The listbox is populated from a column on the spreadsheet. This list is in alpha order.

The user enters text into the textbox. As he does so, a short macro is called by a change event. This searches the list using the string in the textbox and, if a match is found, highlights it. This is working fine.

In addition, if the user can see the entry he wants in the listbox, he can click the entry and the click event will trigger a macro that will populate the textbox with the relevant string from the listbox. This also works fine.

The issue arise when an entry in the listbox is highlighted because of a match to the data from the textbox (bearing in mind that the search data does not have to be the full length string that is in the Listbox). At this point, clicking on the highlighted listbox entry does not fire the click event and the full string is not transfered to the textbox.

To clarify...

Lets assume that the data is - say - Redcurrant, which is present in the list.

The user, typing in the textbox types 'R' and the highlight jumps to the first entry starting with 'R'.
The user types 'e' and the highlight jumps to the first entry starting 'Re'
The user types 'd' and the highlight jumps to the first entry starting 'Red' which, for the sake of argument is 'Redcurrant'

What should happen now is that the user, realising that the entry he wants is already on the list, clicks the entry in the listbox, and the complete string (i.e. 'Redcurrant') is transfered to the textbox. (In the full project, this also triggers other things but lets not complicate matters). Unfortunately, this doesn't work.

Does anyone know why the listbox_click event doesn't trigger when the 'clicked' entry is highlighted?

There are a couple of workarounds...

1) Instruct the user to click another entry before clicking the required one... this works but is very inelegant.http://www.excelforum.com/images/smilies/frown.gif
2) Use the 'DblClick' event which doesn't exhibit the same behaviour and works fine... but requires a double click.
http://www.excelforum.com/images/smilies/mad.gif

Thanks for any assistance

Robin

Try using the mouseUp rather than Click - it will fire regardless

Actually Andy in a round about way, you answered first here:

http://www.excelforum.com/excel-prog...-listview.html

I seem to think that the click event in a listbox works in a similar way to the item click in a listview, which actually may be better named as selection_changed rather than click.

Andy/Kyle

Many thanks for that... works perfectly. Guess it never occured to me that 'Mouseup' would work differently from 'Click.

Originally Posted by JosephP

if you're matching the start of the entry why not use a combobox?

Joseph,

I thought of using a combobox and, to be fair, I could have done but it wouldn't do exactly what I want. In particular, the information shown in the Listbox includes more information than I want to transfer to the textbox. (The description above doesn't say this but that is really a description of a very much pared down version of the form that I have been using to resolve this and other issues).

Anyway, everything is working fine now.

Thanks to all

Rob

@ Rob Frankham

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

@ Cutter

Thanks for the advice. The protocol of this forum is new to me. I'll bear it in mind.

Rob