Handling Multiple Control Types in a WithEvents Class

In the article, Using WithEvents to Encapsulate Event Handling Code, I walked through the steps of creating a class module that uses WithEvents to handle the KeyDown event of a textbox.  Let's expand on that class module to handle other types of controls, like checkboxes and combo boxes.

The Existing Code

Here is the relevant code from the last article:

' --== weArrowKeyControl class module ==--
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

I defined a module-level TextBox variable using the WithEvents keyword.  This allowed me to create event handlers for that TextBox using the two dropdowns at the top of the code window.

The problem with this approach is that it only supports text boxes.  But to fully implement Excel-like navigation on a continuous form, we need to be able to handle events for other common controls, such as check boxes and combo boxes.

Why not use a single generic Control object?

This is a good first thought.  Instead of declaring Private WithEvents mTextBox As TextBox, we could declare Private WithEvents mControl As Control.  There's only one problem: the generic Control object does not expose any events.

So, if we want to handle the KeyDown event for multiple control types, we'll need to declare separate module-level variables for each type.

Private WithEvents mTextBox As TextBox
Private WithEvents mComboBox As ComboBox
Private WithEvents mCheckBox As CheckBox

We also need to handle the KeyDown event separately for each type:

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

Private Sub mComboBox_KeyDown(KeyCode As Integer, Shift As Integer)
    ArrowKeyNav KeyCode, Shift, mComboBox.Parent
End Sub

Private Sub mCheckBox_KeyDown(KeyCode As Integer, Shift As Integer)
    ArrowKeyNav KeyCode, Shift, mCheckBox.Parent
End Sub

Do we need a separate public property for each type, too?

Thankfully, the answer to this question is no.  We can expose a generic Control property.  In the Property Set procedure, we can then assign the passed control to the appropriate module-level variable based on the control's type:

Public Property Set Control(Ctl As Control)
    Select Case Ctl.ControlType
    Case acTextBox:  Set mTextBox = Ctl
    Case acComboBox: Set mComboBox = Ctl
    Case acCheckBox: Set mCheckBox = Ctl
    Case Else: Throw "Unsupported Control Type: {0}", Ctl.ControlType
    End Select
    
    Ctl.OnKeyDown = "[Event Procedure]"
End Set

The Code: weArrowKeyControl Class (Revised)

Here is the updated code using these new techniques:

'--== weArrowKeyControl class ==--

Option Compare Database
Option Explicit

Private WithEvents mTextBox As TextBox
Private WithEvents mComboBox As ComboBox
Private WithEvents mCheckBox As CheckBox

Public Property Set Control(ctl As Control)
    Select Case ctl.ControlType
    Case acTextBox:  Set mTextBox = ctl
    Case acComboBox: Set mComboBox = ctl
    Case acCheckBox: Set mCheckBox = ctl
    Case Else: Throw "Unsupported Control Type: {0}", ctl.ControlType
    End Select
    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 mComboBox_KeyDown(KeyCode As Integer, Shift As Integer): ArrowKeyNav KeyCode, Shift, mComboBox.Parent: End Sub
Private Sub mCheckBox_KeyDown(KeyCode As Integer, Shift As Integer): ArrowKeyNav KeyCode, Shift, mCheckBox.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

Calling weArrowKeyControl

There's one last thing I want to point out, because it may not be obvious to everybody.  When you call this class from your form's code-behind module, you should use a separate instance of this class for every one of your controls.

For example, let's say you have a form with four textboxes, a combobox, and a checkbox named, respectively:

• tbID
• tbProductCode
• tbProductName
• tbListPrice
• cbSupplierID
• chkDiscontinued

Now, imagine that you have the following code:

Dim ArrowKeyControl As New weArrowKeyControl

Private Sub Form_Load()
    Set ArrowKeyControl.Control = Me.tbID
    Set ArrowKeyControl.Control = Me.tbProductCode
    Set ArrowKeyControl.Control = Me.tbProductName
    Set ArrowKeyControl.Control = Me.tbListPrice
    Set ArrowKeyControl.Control = Me.cbSupplierID
    Set ArrowKeyControl.Control = Me.chkDiscontinued
End Sub

How do you think this form will behave?

If you guessed that tbID, tbProductCode, and tbProductName will use the default Access arrow key behavior and that tbListPrice, cbSupplierID, and chkDiscontinued will use our special arrow key behavior, then you are correct.

Each time you assign a new text box to the write-only Control property, it overwrites the contents of the internal mTextBox variable.  The combo box and checkbox both use the special behavior because they are assigned to the internal mComboBox and mCheckBox internal variables.  Since no other combo boxes or check boxes are being assigned, they are never overwritten.

So, while a single instance of the weArrowKeyControl class can support up to three controls–as long as they are all different control types–actually doing that would result in fragile code that could be easily broken by a future developer (like you yourself).

Thus, it's much safer to rewrite the above code as:

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

Private Sub Form_Load()
    Set akID.Control = Me.tbID
    Set akListPrice.Control = Me.tbListPrice
    Set akProductCode.Control = Me.tbProductCode
    Set akProductName.Control = Me.tbProductName
    Set akDiscontinued.Control = Me.chkDiscontinued
    Set akSupplierID.Control = Me.cbSupplierID
End Sub

An Even Better Way?

If this still seems like more boilerplate than necessary, that's because it is.  We can further reduce the amount of boilerplate code we need to implement this Excel-style arrow key navigation.  In fact, we can get it down to only two lines of code in the calling form.

But that's an article (or Access User Group presentation!) for another day.