I have an application that displays a list of “documents” for a particular product. Most of the documents are pdfs, but some are iso images for burning a CD. The display sheet shows each CD associated with the product, and for each CD the iso, the label that’s stuck to the CD, and the pdfs that should accompany it. The user selects the product, operating system, and version from comboboxes to display the appropriate documents.
Normally, the code for a combobox lives in the containing sheet’s class module (like Sheet1). However, good coding practice dictates that all of the VBA be in the add-in and not in the template. To accomplish this, I made a custom class module to house the three comboboxes and respond to their events. The class is named CSheetEvents and contains these variable declarations:
|
1 2 3 |
Private WithEvents mobjProductCombo As ComboBox Private WithEvents mobjSystemCombo As ComboBox Private WithEvents mobjVersionCombo As ComboBox |
The variables are private to the class module and I use Property Get and Property Set procedures to assign them. An example is:
|
1 2 3 4 5 6 7 8 9 10 11 |
Public Property Get ProductCombo() As ComboBox Set ProductCombo = mobjProductCombo End Property Public Property Set ProductCombo(objProductCombo As ComboBox) Set mobjProductCombo = objProductCombo End Property |
When the user selects the appropriate menu item, the code opens the template and assigns the three comboboxes. The relevant portion of that code is:
|
1 2 3 4 5 |
With Wb.Sheets(1).OLEObjects Set gclsSheet.ProductCombo = .Item(“cbxProduct”).Object Set gclsSheet.SystemCombo = .Item(“cbxSystem”).Object Set gclsSheet.VersionCombo = .Item(“cbxVersion”).Object End With |
The variable gclsSheet is a global variable that holds an instance of the CSheetEvents so that it doesn’t go out of scope. With the comboboxes properly assigned inside the class, they will now respond to events. The change event of cbxProduct fills cbxSystem. The change event of cbxSystem fills cbxVersion. And the change event of cbxVersion lists the CDs and hyperlinks to all of the documents.
When the user has printed the documents, he closes the sheet. Since I’ve used a template, there is an unsaved document named AllCDConfigurations1 – a workbook created from the template AllCDConfiguration.xlt. I don’t want the user to be faced with a message to save this document, so I also included a workbook object in my class module. By defining a workbook variable with the WithEvents keyword, I can capture the Before_Close event and eliminate that save message.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Private Sub mobjWb_BeforeClose(Cancel As Boolean) On Error Resume Next Cancel = True Application.EnableEvents = False If Not EnableMenu(False) Then Err.Raise glHANDLED_ERROR mobjWb.Close False Application.EnableEvents = True Me.Terminate End Sub |
The Cancel = True part stops the normal close operation and the mobjWb.Close False part closes the workbook without saving changes.
I have demonstrated something similar for handling series of option buttons on a worksheet:
http://www.jkp-ads.com/articles/ControlHandler00.htm
This morning I was wondering if I could put a text box in a class. I figured I would find something about it on DDOE if I searched hard enough. Lo and behold, this was the top entry at the time! Thanks very much.
It appears, however, that some events don’t fire, even when run from an interactive form. It is the events that aren’t supposed to fire when the event occurs through code. So even if I am using the form interactively, the textbox _AfterUpdate event doesn’t fire because the event handler is residing in a class. Am I doing this wrong?
Bob: There are few Textbox events that aren’t exposed via automation and AfterUpdate is one of them. If I remember correctly, the AfterUpdate event is at the container layer (userform or sheet) so it shows in the container class module. But since only the control is defined in your custom class module, there is no container layer to house that event. If you can figure out how to use the Change event, that’s about the only work-around I know.
I didn’t realize this about a textbox’s AfterUpdate events. I often use its Change or Exit event for the same purpose.
Dick: What happens if you tell the class about the container?
Jon, I’m almost certain that the Exit event is also a container event, and that’s why Dick said that the Change event is the only work-around, and of course it’s not a very good one.
jkp: I tried that yesterday. I put a withevents userform variable and a withevents textbox variable in a custom class, but I wasn’t able to “contain” one inside the other – they were both just contained in the custom class. The linking mechanism must not be exposed. Also, you can’t WithEvents another custom class because it doesn’t “source automation objects” or some such thing.
I feared so much. Thanks for trying though.
Dick wrote:
“jkp: I tried that yesterday. I put a withevents userform variable and a withevents textbox variable in a custom class, but I wasn’t able to “contain” one inside the other – they were both just contained in the custom class. The linking mechanism must not be exposed. Also, you can’t WithEvents another custom class because it doesn’t “source automation objects” or some such thing.”
It gets worse. I’ve done — or at least tried to do — things along these lines for several years now.
One can declare a WithEvents MSForms.Control variable. Once that’s done, it exposes several events including Enter and Exit.
However, when one tries to set the variable to a real control, VBA raises a runtime error about “control does not support these events.” Even if one just declares the variable and has no event procedures, VBA still raises the same error.
I have a fair bit more on this subject. Hopefully, I’ll put it on my website over the weekend. The material includes a tutorial on how to deal with certain events raised for userform controls (very much like Dick’s blog post), an extension to create a jigsaw puzzle in an userform, and a large “black box” class module to deal with events for every standard userform control.
It sounds new for me and I will do this experiment.Thanks.
Tushar Mehta wrote:
Anyone know if this is available?
thx