MsForms.Control Event Class

[AikonEnohp via OfficeKB.com]

Is it possible to trap MsForms.control events using a Class? I need to trap
the BeforeUpdate and Enter events for all textbox controls located in multi-
page control. Using a class to trap these events would eliminate a bunch of
redundant code.

 

[Tom Ogilvy]

Only native events. These events are provided by the controls container
object and to the best of my knowledge, can not be trapped by a class.

 

[AikonEnohp via OfficeKB.com]

Thank you for responding. The odd thing is that I can create a class that
reveals these events but a run-time error occurs when using it. See below

____Class________________________________________________

Option Explicit

Private WithEvents mctlControl As MSForms.Control

Public Property Set Control(ctlNew As MSForms.Control)
Set mctlControl = ctlNew
End Property

Private Sub mctlControl_Enter()
MsgBox mctlControl.Name & " On Enter Event"
End Sub
------------------------------------------------------------------------------
------


____Sub_________________________________________________

Public Sub InitializeControlEvents()
Dim ctlControl As MSForms.Control
Dim frmForm As UserForm
Dim clsControlEvents As clsControlHandler

Set mcolControlEvents = New Collection
Set frmForm = FTestForm

'Loop through all the controls
For Each ctlControl In FTestForm.Controls
If TypeName(ctlControl) = "TextBox" Then

'Create a new instance of the event handler class
Set clsControlEvents = New clsControlHandler

'Tell it to handle the events for the text box
Set clsControlEvents.Control = ctlControl

'Add the event handler instance to the collection
mcolControlEvents.Add clsControlEvents
End If
Next
End Sub
------------------------------------------------------------------------------
-

All of the generic control events (“AfterUpdate” BeforeUpdate” ,“Enter” &
“Exit ” are available to the class and can be selected from the dropdown in
the in the code window. That not withstanding, a run-time error occurs when
the sub procedure is executed.

 

[Peter T]

I think you have demonstrated exactly what Tom stated.

You can trap some of the Textbox events, but not the four particular events
you mention. Maybe you can workaround with the others that can be exposed.
Try something like the following.

' Userform code
Dim colTB As Collection

Private Sub UserForm_Initialize()
Dim ctr As Control
Dim m As Long, i As Long

Set colTB = New Collection
For m = 0 To Me.MultiPage1.Pages.Count - 1

For Each ctr In Me.MultiPage1.Pages(m).Controls
If TypeName(ctr) = "TextBox" Then
i = i + 1
colTB.Add New Class1
Set colTB(i).tb = ctr
colTB(i).TBdata(ctr.Name, m, Me.MultiPage1.Pages(m).Name) = i
colTB(i).tbValue = ctr.Value 'should do this with property
End If
Next
Next

End Sub

' Class1 code
Option Explicit
Public WithEvents tb As MSForms.TextBox
Public idTB As Long
Public sTBname As String
Public idMultP As Long
Public sMultiP As String
Public tbValue

Public Property Let TBdata(sTB As String, nPage As Long, sPage As String, d
As Long)
sTBname = sTB
idMultP = nPage
sMultiP = sPage
idTB = d
End Property


Private Sub tb_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift
As Integer)
If KeyCode = 13 Then
MsgBox "Enter pressed in" & vbCr & _
sMultiP & " Pg-no." & idMultP & vbCr & _
sTBname & " TB-no." & idTB & vbCr & _
"old value " & tbValue & vbCr & _
"new value " & tb.Text
tbValue = tb.Text
End If
End Sub

Hopefully the above will instanciate withevents class's for all textboxes in
all pages of a multipage.

To simulate the missing events you would need to trap everything that puts a
textbox in focus, eg mouse & key arrows & tabs. Store the text box value and
compare changes on leaving the textbox & restore, following with key & mouse
events that exit the textbox. A bit of work!

Regards,
Peter T