Handling Worksheet control events using a class module.
Pages in this article
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 (27-3-2006 06:58:25) deeplink to this comment
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 (27-3-2006 09:47:42) deeplink to this comment
Hi Rogerio,
Did you declare the Collection variable at the top of the module?
Comment by: JK (16-8-2007 11:35:02) deeplink to this comment
Thanks for the easy to follow example. It helped a lot.
Comment by: Harry (9-4-2008 22:12:04) deeplink to this comment
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 (4-2-2009 00:30:13) deeplink to this comment
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 (4-2-2009 01:34:45) deeplink to this comment
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 (14-4-2010 21:19:23) deeplink to this comment
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 (14-4-2010 21:22:03) deeplink to this comment
Hi Steve,
Can you send me your file?
Comment by: Hiran de SIlva (28-2-2011 15:33:03) deeplink to this comment
Hi JKP,
How can I adapt the code to trap a Worksheet_BeforeDoubleClick event?
Thanks for your good work!
Hiran
Comment by: Jan Karel Pieterse (28-2-2011 22:21:05) deeplink to this comment
Hi Hiran,
It isn't hard to do, check out Chip's site on Application events:
http://www.cpearson.com/Excel/AppEvent.aspx
But why not use the event in the worksheet's module?
Comment by: ABabeNChrist (12-4-2011 22:16:52) deeplink to this comment
Very nice and easy to follow, Thanks
Comment by: Harry (13-2-2012 11:28:01) deeplink to this comment
Jan Karel,
I'll comment and ask a question in English.
For what I saw, it looks promising for my purpose.
But...
I'm looking for a simular solution, regarding command buttons.
(On the sheet. Excel 2003)
I have a set of buttons which apart from 2, should be disabled untill the action of either of those two available ones took place.
It would even be better if they were all disables untul a value is entered in a (named) range (= a cell).
The 'Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)' 'kills' my 'modeless' form, which contents I need to perform tasks on other sheets.
Could ypu give me a clue/hint?
Thanks
Harry
AKA
Herrie
Comment by: Jan Karel Pieterse (14-2-2012 03:02:46) deeplink to this comment
Hi Harry,
Adding ActiveX controls to a worksheet indeed resets your VBA project. Since you have a modeless form showing anyway, why not put the buttons on the form?
Comment by: Harry (14-2-2012 03:59:21) deeplink to this comment
Hi Jan Karel,
It is all about a lessenplan and changes (daily) due to illness of leave.
The modeless form is only a 'help', shown after a list of possiblities is created.
In the 'main sheet' I enter the 'name of a collegue, who is ill or whos has leave of absense.
I klick the 'mark as..' button, which colours the 'lessons' of this collegue in the 'class' schedule.
The next step is to create a list of all available collegues at these marked lessons. Here the modeless form is a help, I can switch sheets to check if it is an option or not.
When a solution ius an option I mark it as such in the 'class' schedule.
So the 'worksheet
This helps e to create the daily 'changes'.
Further in the idea I sum up the changes and clean the sheet, making a copy of the sheet with changes for later use or to archive.
In order to prevent clicking wrong buttons (because the data is not present) I want them to be 'disabled'.
Correct data in one or more cells 'enables' the 2 main buttons (Ill and Absense)..
Presently this 'looking for solutions' is done by hand and on paper. Flipping through 10 pages... So 'Workbook_SheetChange isn't working as I like.
Comment by: Jan Karel Pieterse (14-2-2012 07:06:11) deeplink to this comment
Hi Harry,
Form control buttons can easily be disables/enables from code. And it does not reset your code either:
Comment by: Robert (25-4-2013 14:40:37) deeplink to this comment
I have read your article regarding Events Using Class Module but I can't find this part that relates to how to intercept triggering the event comming from one control to other control could do something.
I'm new in Class's World so I have a problem...
The problem is How to fill TextBox by values (Scroll.Min to Scroll.Max) ( comming from ) ScrollBar_Change_Event if I have added both controls to UserForm at run-time ( I mean TextBox and ScrollBar ). ?
I have UserForm and Class module. If I add only ScrollBar at run-time , but TextBox at design-time there is no problem .
The problem appears only if I have added both these controls at run-time.
How trap event ( Scroll_Change_Event) to fill TextBox values from range: (Scroll.Min= something and Scroll.Max=something) when both controls were made in run-time mode.
I have used standard code to add the mentioned controls like "Set" etc.
I was searching many web page to get this subject but failed.
I don't want you to write me the detailed code
but I think you can make generalize problem - How to trap the triggering event from one control to change something in another control to assume that controls were added at Run-Time Mode.
Thanks in advance for reply
Robert
Comment by: Jan Karel Pieterse (25-4-2013 16:13:58) deeplink to this comment
Hi Robert,
OK, in general terms then :-)
- Make sure the class has a property to which you can pass a pointer to the form itself:
Private Sub Class_Terminate()
Set moForm = Nothing
End Sub
Public Property Get Form() As Object
Set Form = moForm
End Property
Public Property Set Form(oForm As Object)
Set moForm = oForm
End Property
In your Userform, set the form pointer to the class:
Set .Form = Me
End With
Now make sure that if you are combining two controls that you use a naming convention, such as scrMonth and tbxMonth.
Then you can do this in the class:
With Form.Controls(Replace(ctl.Name, "scr", "tbx"))
.Value = .Value + 1
End With
End Sub
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.