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

Showing last 8 comments of 17 in total (Show All Comments):

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:

Worksheets("Foo").Buttons("MarkAs").Enabled = False

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 moForm As Object

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:

With clsTheClass
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:

Private Sub ctl_Change()
    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.