Using WithEvents to Encapsulate Event Handling Code

In my article, The ArrowKeyNav Routine, I presented a Sub that you can use to override the default handling of the up and down arrow keys on a continuous form.  In a followup article, Beautiful Blocks of Boilerplate, I demonstrated a technique for formatting code to improve readability of a group of procedures that each calls the same subroutine or function.  

That technique only addresses a symptom of the ArrowKeyNav approach, though, namely that we need to call the routine from within each control's KeyDown event handler.  A better approach would be to avoid the requirement to create an event handler procedure for every control in the first place.  How do we do that?

Calling a function from the property sheet event tab

One option is to select multiple controls at once and then set the "On Key Down" event to the name of a public function instead of "[Event Procedure]."  This can be a useful time-saving technique, but it has one major drawback: you don't have access to the parameters that are raised with the event.  In other words, it won't work with the ArrowKeyNav routine, because ArrowKeyNav relies on the KeyCode and Shift arguments being available.

A WithEvents Listener Class

Defining a local textbox object

Since we need access to the event parameters, KeyCode and Shift, our best option is to create a so-called "Listener" class.  

To do this, we start by creating a class module (you can't use WithEvents with a standard code module).  At the top of the class module, we add a module-level variable to represent the text box.

' --== weArrowKeyControl class ==--
Option Explicit
Option Compare Database

Private WithEvents mTextBox As TextBox

Next, we need some way to assign the textbox from our form to the internal textbox object within our class module.  Since we won't be referencing the internal textbox object from outside the class, we can make it a write-only property by exposing only a Property Set.  There's no need for a Property Get.

While we're at it, we'll also force the control's OnKeyDown property value to "[Event Procedure]."  We need to do this to ensure that the event handler we're about to implement actually gets called.  Without this line of code, we would have to remember to manually set the value of the control's On Key Down box in the Properties window in form design view.

Public Property Set TextBox(Ctl As TextBox)
    Set mTextBox = Ctl
    Ctl.OnKeyDown = "[Event Procedure]"
End Property

Adding the KeyDown event handler

Now, we need to add the KeyDown handler for our internal textbox variable, mTextBox.  Because we used the WithEvents keyword when we declared mTextBox, it appears in the object dropdown box on the left side at the top of the code window:

After you choose mTextBox from the dropdown on the left, choose the KeyDown event from the procedure dropdown on the right:

When you first choose a WithEvents object from the left dropdown, the VBE will generate a procedure outline for that object's default event. For the TextBox object, that is apparently the BeforeUpdate event.  We're not going to handle that event in this class module, so just delete that code:

Private Sub mTextBox_BeforeUpdate(Cancel As Integer)

End Sub

You should still have an mTextBox_KeyDown() subroutine in your class now.  We're going to call the ArrowKeyNav routine from the KeyDown handler:

Private Sub mTextBox_KeyDown(KeyCode As Integer, Shift As Integer)
    ArrowKeyNav KeyCode, Shift
End Sub

Integrating the ArrowKeyNav routine

The question now is where do we put the ArrowKeyNav procedure?  In the article above, I included the routine in the code-behind of the form.  When writing code in a form's code behind, we have access to the form instance via the reserved name Me.  

If we just copy and paste the ArrowKeyNav procedure into the weArrowKeyControl class module, the reserved name Me will refer to an instance of the weArrowKeyControl class and not the form.  To deal with this, we will make the Form object a parameter of the ArrowKeyNav routine.

Here's what the refactored ArrowKeyNav procedure looks like inside the weArrowKeyControl class module:

Private Sub ArrowKeyNav(ByRef KeyCode As Integer, Shift As Integer, Frm As Form)
    If Shift <> 0 Then Exit Sub
    
    Dim SaveKeyCode As Integer
    SaveKeyCode = KeyCode
    KeyCode = 0
    
    Select Case SaveKeyCode
    Case vbKeyUp
        Frm.Recordset.MovePrevious
        If Frm.Recordset.BOF Then Frm.Recordset.MoveNext
    Case vbKeyDown
        If Frm.NewRecord Then Exit Sub
        Frm.Recordset.MoveNext
        If Frm.Recordset.EOF Then
            If Frm.AllowAdditions Then
                Frm.Recordset.AddNew
            Else
                Frm.Recordset.MovePrevious
            End If
        End If
    Case Else
        KeyCode = SaveKeyCode
    End Select
End Sub

Calling the refactored ArrowKeyNav routine

Since we changed the ArrowKeyNav routine to require a form object as its third argument, we need to update our call to the routine.  From where might we get such a form object?

We could explicitly set the form object as a write-only property of our class–as we did for the textbox–but that's probably* overkill.  Instead, we can use the textbox control's Parent property to return its containing form.  

(* NOTE: There are some corner cases where the control's parent is something other than the containing form object, but we're going to set those aside to keep this article focused on the task at hand.)

ArrowKeyNav KeyCode, Shift, mTextBox.Parent

Using the weArrowKeyControl class

So, how do we actually use this class from our continuous form?  Let's assume we have a continuous form with the following text box controls:

• tbID
• tbProductCode
• tbProductName
• tbListPrice

We would start by declaring a module-level variable for each corresponding textbox in the form's code-behind module header:

Dim akID As New weArrowKeyControl
Dim akListPrice As New weArrowKeyControl
Dim akProductCode As New weArrowKeyControl
Dim akProductName As New weArrowKeyControl

Finally, we initialize each of these variables in the Form_Load event:

Private Sub Form_Load()
    Set akID.TextBox = Me.tbID
    Set akListPrice.TextBox = Me.tbListPrice
    Set akProductCode.TextBox = Me.tbProductCode
    Set akProductName.TextBox = Me.tbProductName
End Sub

The Code: Putting It All Together

If you've followed along (and I haven't made any mistakes of my own), this is what your code should look like:

' --== MySampleForm code behind ==--

Option Compare Database
Option Explicit

Dim akID As New weArrowKeyControl
Dim akListPrice As New weArrowKeyControl
Dim akProductCode As New weArrowKeyControl
Dim akProductName As New weArrowKeyControl


Private Sub Form_Load()
    Set akID.TextBox = Me.tbID
    Set akListPrice.TextBox = Me.tbListPrice
    Set akProductCode.TextBox = Me.tbProductCode
    Set akProductName.TextBox = Me.tbProductName
End Sub

' --== weArrowKeyControl class module ==--

Option Compare Database
Option Explicit

Private WithEvents mTextBox As TextBox

Public Property Set TextBox(Ctl As TextBox)
    Set mTextBox = Ctl
    Ctl.OnKeyDown = "[Event Procedure]"
End Property

Private Sub mTextBox_KeyDown(KeyCode As Integer, Shift As Integer)
    ArrowKeyNav KeyCode, Shift, mTextBox.Parent
End Sub

Private Sub ArrowKeyNav(ByRef KeyCode As Integer, Shift As Integer, Frm As Form)
    If Shift <> 0 Then Exit Sub
    
    Dim SaveKeyCode As Integer
    SaveKeyCode = KeyCode
    KeyCode = 0
    
    Select Case SaveKeyCode
    Case vbKeyUp
        Frm.Recordset.MovePrevious
        If Frm.Recordset.BOF Then Frm.Recordset.MoveNext
    Case vbKeyDown
        If Frm.NewRecord Then Exit Sub
        Frm.Recordset.MoveNext
        If Frm.Recordset.EOF Then
            If Frm.AllowAdditions Then
                Frm.Recordset.AddNew
            Else
                Frm.Recordset.MovePrevious
            End If
        End If
    Case Else
        KeyCode = SaveKeyCode
    End Select
End Sub

Recap and Next Steps

In this article, we've successfully gone from this...

...to this...

That's certainly an improvement, but you may have noticed something.  We're no longer handling the Combo Box and Checkbox controls from our sample form.  That's something we'll have to tackle in the next article.

Image by Tom Staziker from Pixabay