Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: Is it possible to sink a form's events in a class module ?
Hello !
Much is said in the subject. Using "Private WithEvents frm AS frmMyForm" gives a compile error : "object is not a source of automation events" Using "Private WithEvents frm AS Object" gives a compile error : "Identifier expected" Using "Private WithEvents frm AS MSForms.UserForms" works, but it won't allow me access to some events (such as "QueryClose") I'd like to sink in the class module. Any idea ? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: Is it possible to sink a form's events in a class module ?
As you say the QueryClose event is not exposed to WithEvents, nor are all
events of all userform controls. Not sure why you'd need to sink such an event in a class module though could do something like this - 'userform Dim clsFrm As Class1 Private Sub CommandButton1_Click() Unload Me End Sub Private Sub UserForm_Initialize() Me.Caption = "Test QueryClose" Set clsFrm = New Class1 Set clsFrm.frm = Me End Sub Private Sub UserForm_QueryClose(Cancel As Integer, _ CloseMode As Integer) Cancel = clsFrm.QClose(CloseMode, Me.Caption) End Sub 'Class1 Public WithEvents frm As MSForms.UserForm Public Function QClose(cm As Integer, sCap As String) As Boolean If cm = 0 Then ' little x QClose = MsgBox(" Sure you want to close " & _ sCap, vbYesNo, "CloseMode = " & 0) = vbNo Else MsgBox "Bye" End If End Function Private Sub frm_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) frm.Caption = X & " " & Y ' places text on form End Sub Regards, Peter T "Michel S." wrote in message ... Hello ! Much is said in the subject. Using "Private WithEvents frm AS frmMyForm" gives a compile error : "object is not a source of automation events" Using "Private WithEvents frm AS Object" gives a compile error : "Identifier expected" Using "Private WithEvents frm AS MSForms.UserForms" works, but it won't allow me access to some events (such as "QueryClose") I'd like to sink in the class module. Any idea ? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: Is it possible to sink a form's events in a class module ?
Thanks for your reply..
Not sure why you'd need to sink such an event in a class module I made a class module which is using a set of controls (ListBox, cmdButtons, etc..) to form a "generic-show-sort-and-pic" controls set. This set of controls may appear on different forms. In other words, the controls-set is generic, but not the form it is on. Behind these controls are procedures needing sometimes to interact with the form they are on. An obvious example is the "Close" button which closes the form after some internal housekeeping. Another example is the listbox "dblClick" event which needs to hide the form at some point. Since the forms properties and methods aren't all availiable in a class module, this makes it hard to do without duplicating code. On the other side, clicking on the form's "X" should execute the same code as the cmdClose_click event. If the QueryClose event can only be sinked in the form module while the cmdClose_Click is processed in the class module, you need to either duplicate the code or find a "creative" way to use the same code for both. Hope this clarify why I want to put all related code in the class module. Sorry if this seems basic, but I'm used to Access where you can pass a form object to a class module and all its properties, methods and events are availiable to the class module as any other object.. I have a hard time understanding why it is not the same in Excel.. MS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: Is it possible to sink a form's events in a class module ?
To handle the close button, I put this code into all of my userform code
modules: ''================================================ Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True btnCancel_Click End If End Sub ''================================================ I don't worry too much about repeating this code in each module. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Michel S." wrote in message ... Thanks for your reply.. Not sure why you'd need to sink such an event in a class module I made a class module which is using a set of controls (ListBox, cmdButtons, etc..) to form a "generic-show-sort-and-pic" controls set. This set of controls may appear on different forms. In other words, the controls-set is generic, but not the form it is on. Behind these controls are procedures needing sometimes to interact with the form they are on. An obvious example is the "Close" button which closes the form after some internal housekeeping. Another example is the listbox "dblClick" event which needs to hide the form at some point. Since the forms properties and methods aren't all availiable in a class module, this makes it hard to do without duplicating code. On the other side, clicking on the form's "X" should execute the same code as the cmdClose_click event. If the QueryClose event can only be sinked in the form module while the cmdClose_Click is processed in the class module, you need to either duplicate the code or find a "creative" way to use the same code for both. Hope this clarify why I want to put all related code in the class module. Sorry if this seems basic, but I'm used to Access where you can pass a form object to a class module and all its properties, methods and events are availiable to the class module as any other object.. I have a hard time understanding why it is not the same in Excel.. MS |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003: Is it possible to sink a form's events in a class module ?
Seems you will have to adapt your Access form methods for Excel in view of
not all events are exposed outside the form module. For your arrangement perhaps a slight modification to Jon's QueryClose method Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True theClass.btnCancel_Click End If End Sub Where theClass is reference to the particular instance of the withevents cancel button in say a collection or an array, and you have changed the default Private btnCancel_Click() to Public btnCancel_Click() However the above does similar to the suggestion in my previous post, albeit in a different way. If you have complex code to manipulate form controls this might be done in a code in a normal module. This code can reference the form in a number of ways, - a global reference to the form - simply the form's name as the object reference - a ref to the form passed as an argument The normal code might also refer to a collection or array of withevents class's to loop controls in those classes. The collection or array could be stored as public in the form as a clean way of destroying the class's when the form is closed or a ref to the form is destroyed. FWIW I have code to manipulate the form and its controls in the form, multiple withevents class's and in normal modules. Regards, Peter T "Michel S." wrote in message ... Thanks for your reply.. Not sure why you'd need to sink such an event in a class module I made a class module which is using a set of controls (ListBox, cmdButtons, etc..) to form a "generic-show-sort-and-pic" controls set. This set of controls may appear on different forms. In other words, the controls-set is generic, but not the form it is on. Behind these controls are procedures needing sometimes to interact with the form they are on. An obvious example is the "Close" button which closes the form after some internal housekeeping. Another example is the listbox "dblClick" event which needs to hide the form at some point. Since the forms properties and methods aren't all availiable in a class module, this makes it hard to do without duplicating code. On the other side, clicking on the form's "X" should execute the same code as the cmdClose_click event. If the QueryClose event can only be sinked in the form module while the cmdClose_Click is processed in the class module, you need to either duplicate the code or find a "creative" way to use the same code for both. Hope this clarify why I want to put all related code in the class module. Sorry if this seems basic, but I'm used to Access where you can pass a form object to a class module and all its properties, methods and events are availiable to the class module as any other object.. I have a hard time understanding why it is not the same in Excel.. MS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL-2003 - Class Module - Controls declared "withEvents" don't sink events.. | Excel Programming | |||
Running procedures on events xl2003 | Excel Programming | |||
unload userform from within one of that form's beforeupdate events? | Excel Programming | |||
Variable from a sheet module in a class module in XL XP | Excel Programming | |||
What events can be captured in a Class Module? | Excel Programming |