Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Control Events > Conclusion
Deze pagina in het Nederlands

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

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

 


Comment by: Jan Karel Pieterse (2/28/2011 10:21:05 PM)

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 (4/12/2011 10:16:52 PM)

Very nice and easy to follow, Thanks

 


Comment by: Harry (2/13/2012 11:28:01 AM)

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 (2/14/2012 3:02:46 AM)

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 (2/14/2012 3:59:21 AM)

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 (2/14/2012 7:06:11 AM)

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 (4/25/2013 2:40:37 PM)

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 (4/25/2013 4:13:58 PM)

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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].