Excel: How do I Refer to a ListBox Object in a User Class.

[Monster]

I am having problems with listbox events in a excel class Module.

Problems
1. I can not get the listbox to work when declare it as
Public LB as listbox

2. I can get the listbox to work with out the events if I declare it as
Public LB as object

I have tried
Public LB as excel.listbox.. .but .listbox is not a drop down option

I have also tried
Public LB as application.lis tbox....but that did not seem to work.

None of them allow me to add the declare variable "WithEvents "

The goal of my two modules is to allow drag and drop of a collection of user defined documents. The listboxes are multicolumn listboxes and are populate from a array returned from the document object. The listbox and the collection of objects listed in the listbox are variables in a user definded class. Then that user defined class is is added to another user defined class that is just a collection for the former.

It might be easier for me to send code :)

[NeoPa]

I couldn't find anything in the Object Browser (F2) that indicated it may be the correct name for a ListBox. I was able to get the following to compile, but nothing similar related to ListBoxes (which don't have a drop-down element BTW) :

Private WithEvents LB As Worksheet

You would need to identify what the actual object type of a ListBox is called I suspect. I doubt your code would tell us much more at this point.

PS. What version of Excel are you using so I can fix the title properly?

[Monster]

YES....you are absulutly right. You can declare a lot of different object types and they will compile....and they will even work with out the WithEvents declartion variable. You are correct that the mousemove, BeforeDragOver, and BeforeDropOrPas te events are not on the drop down menu, but are more like a module or class initialize and need a _ inbetween them and the object in the module name.

So what does work. When I used the Object browser (F2) and found the event on the bottom of the browser is lists Event MouseMove as a Member of MSForms.ListBox

So I changed my declaration to

Public WithEvents LB as MSForms.ListBox

and now my events trigger in my class modules!!!!!!

1/2 a day wasted for 3 seconds of typing.....

Thankyou all. I am new to your site and it looks really nice!

I am using Excel 2010

[NeoPa]

Luckily, you seem to have misunderstood some of what I was saying :-D

Luckily, as it seems to have led you further than I was able to get. Congratulations on finding your own solution. I'm happy, should you choose to, for you to select your own post #3 as Best Answer.

PS. I just did my earlier search (done on a completely new workbook) on a workbook which had MSForms included and, what do you know, I found what you did. So near, yet so far it seems.

[NeoPa]

By the way, I should add something I meant to mention earlier. It was my experience that an event handler would only trigger in a class module if/when an event handler already existed for the enclosed class. That is to say, the event handler in the class module doesn't trigger if the class it's enclosing (Matching the WithEvents line) doesn't also have code to handle that same event.

[Monster]

NeoPa I actually played around the object browser and found the MSForms.Listbox object type before reading your posting. We did the same thing independently from each other. However it took me hours to figure it out and make my way to the object browser. If I had read your post earlier it may have sent me in the right direction faster. I never really used the object browser for anything before.

Also in the last couple of days I realize that I like using the "question mark" on the top of the object browser to read the help information about the selected object. It is easier than doing a text based help search.

[NeoPa]

Monster:
Also in the last couple of days I realize that I like using the "question mark" on the top of the object browser to read the help information about the selected object. It is easier than doing a text based help search.

Nice tip. It's very much like Context-Sensitive Help which I like and recommend so much.