XL2003: Is it possible to sink a form's events in a class module ?

[Michel S.]

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

 

[Peter T]

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.]

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

 

[Jon Peltier]

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

 

[Peter T]

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