Event Handlers on NEW Objects

[dean12]

I've got an Excel 2003 application. In VBA I have created a userform and that form uses an OCX control. This OCX communicates back to me with an event handler when certain things occur.

The control is named: ABC
The event is : CacheChange

In approach #1, I placed dropped the OCX on the form. Then in the userform code I selected the control, selected the event and coded up the stuff I needed. So I had a SUB defined as:

private sub ABC_CacheChange(.........)

Great. No problems, the handler executes as expected.


Now in approach #2, rather than drop the OCX on the form, I instantiate it after the form is activated and when the user clicks a button on the form. Same control name:

set ABC = New .........

Everything works just fine, I guess, except that the event handler now will no longer be executed.

Any thoughts on the problem?

 

[CautionMP]

dean12,
You need to declare the object ABC [tt]With Events[/tt] in the general declaration section of the form module. When you 'drop' the control on the form VBA does this for you automatically (although it is hidden from you).

Hope this makes sense,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT+08:00) Singapore

 

[dean12]

CMP,

Makes very good sense and indeed it works. Thanks very much.

Turns out I was on this track but had the syntas wrong as I was writing "Dim with events abc" versus "Dim withevents abc".

Unfortunately, while this example program works as you said it would, this trial is not actually my final configuration. VBA appears to allow the "withevents" parameter when in a userform but not just when declaring variables in a simple module. In real life, I don't really want to be in a form. I really want the user to have access to the worksheet and having a userform open may be a problem - certainly if modal, I'm gonna have to check to see if it is possible to open the form modeless.

My other thought on this was to write a class and I believe I can use the "withevents" parameter in the class. Unfortunately, what happens in this event routine cannot be generalized into a class. What would be great would be to be able to instantiate my user defined class which in turn instantiated the object that uses the "withevents" routine. When that routine executes, the routine turns around and passes control out of the class to some external routine that the user has written. I'd call this a callback procedure where we instantiate the class and set a property in the class saying "call me (routine xxx) if you need to". I don't believe VBA offers this level to capability.

Boy if it ain't one thing its another.

 

[combo]

If you do not plan to create a collection, then you have already a place for WithEvent declaration - a workbook/worksheet document modules. They behave like class modules and are already instantiated, you only need to declare variable and assign your object.
You can have modeless userform, this property can be set either in design time (ShowModal property) or when applying Show method (vbModeless parameter).
Some info on using WithEvents you can find in my faq on events in this forum faqs section.

combo