Creating my own combobox class

[Mervil]

I need to create my own combobox, complete with properties and events, that gets added on-the-fly. I'm trying to learn how to create class modules, but I'm having a difficult time finding an answer on how to do the following:

make a class module that displays an editable combobox on the screen.

Any pointers?
Thank you!

[romperstomper]

Based on your previous post, you don't want to do that. What you want is a class that handles the combobox events. For example:
Class called CComboHandler

Please Login or Register  to view this content.

Then in sheet module of sheet with comboboxes on:

Please Login or Register  to view this content.

and you can also call the HookCombos sub from the Workbook_Open event:

Please Login or Register  to view this content.

Demo file attached.

[Mervil]

Well, thats awesome! Thank you! However, I'm unsure of why that works. May I ask you some questions?

1) is it necessary to put the sub HookCombos in the sheet class? or wouldn't it do just fine being in a module, substituting the "me." for "Worksheets("yadayada")."?
2) why do you need to add all the created objects (objhandler) into a collection? you say so that they don't lose focus... what does that mean?
3) i dont understand how the code in the class module (ccombohandler) is complete. the only thing done was to declare a variable, and then an example of event code. Nothing was done for "Combo". What about setting "Combo" equal to something in the class module...?
4) i dont understand how this works: "Set objHandler.Combo = objOLE.Object". How does that NOT overwrite the class module, replacing it with the actual object objOLE.Object?

Thank you!
Im sorry for my questions. Class modules are still so very confusing for me.

[romperstomper]

1. It would be fine in a module, but as it is sheet specific, I put it in the sheet.
2. The variables are declared and created in the HookCombos routine, which means that if you don't store them somewhere (an array would also work) they would lose their values when the routine ended.
3. Assigning an actual combo box to the class happens in the HookCombos routine. Remember that the class is really just a blueprint; you are simply saying that the class will have a combo box and you want to respond to its events.
4. It does assign the actual combo to the class' combo variable - that's why you can intercept the events for that actual combo box.

[Mervil]

I'm still not understanding how the variable "combo" can be functional. In classes, it seems, when you dim a variable, you then have to do something with that variable (use it in a property or procedure) so that when it is used as an object later (objHandler.combo) the software knows what to do with it.

But in the class module, the only code that for the combo variable is a public statement. So how does the software know what to do with it? What DOES the software do with it?

Thank you for your continued help.

[romperstomper]

By declaring the variable WithEvents, you create what is known as an event sink, or event listener. Essentially it says that the class will 'listen' for the events of that control being raised. By adding the combo_change code, you specify what you want to happen when its Change event is raised.
If you are interested in this sort of thing, I would recommend getting hold of a good VBA book, such as the VBA Developers Handbook. You may also want to have a read of Chip's page here.

[Mervil]

Ok. Thank you RomperStomper. I'm starting to understand. My last question is in regards to the actual use of the objects and collection.

So, with excel collections, as in Worksheets, or OLEObjects, the objects contained therein are easily referenced, either by name or index number. How can the objHandler objects that are being added to the colHandlers collection be accessed?

If objHandler.Combo = objOLE.Object, can objHandler.Combo be displayed on the screen as a combobox? Does it have access to all of objOLE.Object's properties, such as borders, font, etc?

Lastly, if any of the properties of the comboboxes on sheet 1 are changed, the association between the combobox and the event class is lost. Why is that? Is there a way to prevent that?

Thank you, very much.

[romperstomper]

The objhandler objects can also be accessed by name or index, though here I did not specify a key when adding to the collection. There shouldn't really be any need to do this though - the sole purpose of the class is to handle events.
The combobox is already on the sheet when you assign it to the objhandler class so that question doesn't really apply. Since Combo is a reference to the actual on-sheet combobox, all the properties are available.
Unfortunately, activex controls are part of the vbproject so adding them or making design time changes, among other things, causes state loss and the collection goes out of scope and gets reset so you have to rerun the code. That's why I try to avoid controls on sheets generally and use Forms controls if possible if I really need controls on sheets.

[Mervil]

I've been trying to take the example that was posted above and re-work it into my own project. However, its just not working. I'm not sure at all why its not, the code is pretty much in there verbatim. RomperStomper, you said that the collection goes out of scope with design-time changes. What if the objects were stored in an array instead of a collection? Would the array also be subject to losing scope and being reset?

[romperstomper]

Yes, you would have the same problem. It's nothing to do with the variable type, it's the fact it gets reset.

[Mervil]

Well, I got the code and the classes to work with my program, but ONLY when i call the HookCombos sub separately, by itself. If I call that sub as part of my macro, even if the call statement is the very last thing the macro performs, it doesn't work. Any ideas please?

thank you so much!

[romperstomper]

You haven't exactly given much to go on, so I'll have to guess. If your code is creating the comboboxes at the same time, then you will need to call the Hookcombos routine using Application.OnTime or your variable goes out of scope as soon as your code ends (since adding combos change the VBProject).

[Mervil]

Hehe, you're right. I haven't provided much info regarding my program. I apologize. I'm still new at this and not sure what sort of info is useful. Yes indeed, the comboboxes are created at the same time. Ill give that timing method a shot. Thank you romperstomper!

[Mervil]

Yay! It worked! Thank you RomperStomper! Finally got it all figured out