Handling Worksheet control events using a class module.

Conclusion

As shown in this article it is possible to replace a multitude of event subroutines by a single class module in combination with an initialisation routine. This is particularly useful when one has a multitude of controls on a single form or worksheet and wants to perform similar actions on a specific event of each control.

Unfortunately, not all events we are used to get are available when we use this method. Some missing events are quite crucial, such as the AfterUpdate event for a textbox.

A sample file can be downloaded here.

Comments

All comments about this page:

Comment by: Rogerio Takejame (3/27/2006 6:58:25 AM)

I've tried to use this method but, instead of using option buttons, I used OLEObjects.Image.
I noticed that it hooks every image to the class module and adds to the collection without any problem, but after the last End Sub, the collection looses all its values. In a comment inside the code you said that the collection should keep its value for the entire workbook life. What am I doing wrong?

Thanks for your help

Rogerio Takejame
Americana - Sao Paulo - Brazil

Comment by: Jan Karel Pieterse (3/27/2006 9:47:42 AM)

Hi Rogerio,

Did you declare the Collection variable at the top of the module?

Comment by: JK (8/16/2007 11:35:02 AM)

Thanks for the easy to follow example. It helped a lot.

Comment by: Harry (4/9/2008 10:12:04 PM)

I have been looking for this to coordinate 12 comboboxes that drive a selective graphing routine for large data such as weather .
If I can adapt it I will semd you a copy.
Thanks Harry S.

Comment by: Richard (2/4/2009 12:30:13 AM)

I have a global combobox in a workbook containing multiple
worksheets. All I want to do for the moment is to place the
combobox event handling code in a global module somewhere
so that it only appears once instead of being duplicated on
every worksheet (I posted this question in the mrexcel forum
and someone suggested I try here...)

Is using a class module really the only way to do this? It
seems like quite a lot of trouble to go to for a relatively
simple task. I would be interested in your comments.

Thanks, Richard.

Comment by: Jan Karel Pieterse (2/4/2009 1:34:45 AM)

Hi Richard,

If you use a control form the control toolbox, then the answer is -unfortunately- yes.

You can however use the controls from the forms toolbar. Those can be tied to one sub in a normal module.

Comment by: Steve Arndt (4/14/2010 9:19:23 PM)

Hi Jan

I've used your code in Handling Worksheet Control Events Using A Class Module. I set it up to work with frames - each containing a textbox and spinbutton placed on a worksheet. It works great!

What I'm struggling to do is figure out how to add code to your code to allow me to dynamically add a frame - with textbox and spinbutton to the worksheet during run time. I can get the controls added but can't get the events to work using the code in the class module.

I've been working on this problem for awhile and can't figure out how to do it and have hit a wall. Any help you could provide would be greatly appreciated.

Regards

Steve

Comment by: Jan Karel Pieterse (4/14/2010 9:22:03 PM)

Hi Steve,

Can you send me your file?

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.