Controls toolbox -Listbox query

[Sun Li]

Hi All,
I need some guidance related to control toolbox object Listbox.

I have created a Excel spread sheet with multiple list box. The users can select multiple selections from these listboxes. I have created a function for listbox1 where in it stores all selected items in a string. I need to do the same for the other listboxes as well but i am not able to create a generic function to fulfill the purpose.

Following is the code used.

Please Login or Register  to view this content.

I did try creating another function to pass the listbox object but it results in a type mismatch error.

Please Login or Register  to view this content.

Could someone provide any suggestions?
Thanks

[mikerickson]

I think these functions will do what you want.
dataFromListBox(1) will return the string you want from ListBox1, datafromListBox(2) from ListBox2, etc.

LBox(3) will return the object ListBox3. You may have to ajust the sheet name in LBox.

Please Login or Register  to view this content.

[Sun Li]

Thanks for the reply. I was thinking more along the lines of passing the listbox to the function itemselect and then referencing the selected items in the function. Is that possible?

[mikerickson]

Please Login or Register  to view this content.

[Sun Li]

Thanks heaps...the keyword 'Object' has done the trick.

Thank you very much.

[mikerickson]

You're welcome.

[mikerickson]

In responce to the PM: I found no problem with passing the object argument. (I'm restricted to UserForm controls) I used both Object and msForms.ListBox object types with success.

I did find a problem. The code above uses the .TopIndex property. That won't work (for me.) I didn't do sufficiant testing before posting that. The code should use the .ListCount property instead.

Please Login or Register  to view this content.