Combobox Multiple Selection or Listbox Dropdown

Thanks Roy, I understand that bit, but I am being frustrated by the fact that I cannot make the listbox act like a dropdown menu. I need it to be just a single line in height (15 in the property) to fit in nicely with my userform design, but if I do that it makes it difficult for the user to understand what he/she has selected. The only other way I can see is to have a larger list box, but this doesn't look so good...

Thanks Roy, I suspected as much, but had hoped that there was some clever trick that I was unaware of...

I appreciate that this is an old thread, but if somebody stumbles across this while googling (like I did) then...

You could use the MouseOver event on your Listbox to increase the height of the box, then the MouseOver event on the Userform to reduce the height back to normal. This should give you the desired effect.

Hi,

Djje87, please could you elaborate on the "MouseOver" event.

I have been searching EVERYWHERE to try and find a way that my ListBox can have a drop down effect.

Basically I have a spreadsheet (Excel 2010) which I need users to be able to select multiple countries. The sheet will be about 200 rows, but this function needs to be available for each row. It is basically a item set up sheet, so users will need to enter their item number, then select the country or countries that the item is valid in. So obviously I cannot have the List Box "open" as there are around 40 countries they can choose from on each row.

Please can you let me know my options here, or any other ideas to format this.

Many thanks,

Andy!

Hi Andy,

My bad, I actually meant ("MouseMove" - typo).

It sounds like you're working directly in a worksheet, rather than a UserForm?

If your 'listbox' is actually a Data Validation list, then you could check out http://www.contextures.com/excel-data-validation-multiple.html for some tips.


If you're using the ActiveX control ListBox in the worksheet, then you should be able to make use of the MouseOver event. In Design Mode, right-click the listbox and select "View Code" - this should open up the VBA window. You should see two dropdown boxes at the top of the Code window, the left one should show the name of your listbox (e.g., "ListBox1") and the right one should contain a list of events. Select the MouseMove event from the right box - this will automatically add a sub to the code window that will run whenever the mouse is moved over the listbox. Just type whatever code you want in this sub, e.g.,

Please Login or Register  to view this content.

.

I was working in a UserForm, so I could use a similair approach but on the UserForm object itself to make the listbox small again when the mouse wasn't over the listbox - but perhaps you could use the "LostFocus" event to resize the listbox.

Hope this helps.

Hi,

Yes, I am indeed working directly in a worksheet not a UserForm.

What you have given me is exactly what I want, however as you have mentioned, I need the box to return to it's "normal" size.

I have a ActiveX Control Listbox which is set to multiple selection (with tick boxes).

So basically if I can implement what you have given me, plus add a function where the Listbox returns to it's normal size once selections have been made and the user "clicks out", it would be perfect!!

Sorry, I am very new to VBA and code so any help would be great!

Thank you.

Andy.

No problem.

If the user clicks the listbox once it's been 'expanded', then the LostFocus event should work (if they don't click it in the first place, it never gets focus in the first place, so it can't be lost!).

Let me know if you get stuck.

Originally Posted by john55

guys, this is an old thread, I think you need to open a new one and ask there for help....

Thanks John.
I stumbled upon this thread about a month ago when I was trying to find an answer - I ended up figuring it out myself, so decided to post my 'solution' here in case anyone else stumbled across it like I did (It seemed the most relevant Google search, despite being an old thread). Looks like Andy was in the same boat.