Many Objects, One Class Module

In my last article about using WithEvents to implement Excel-style arrow-key navigation, Handling Multiple Control Types in a WithEvents Class,  we had improved our weArrowKeyControl class module to the point where we had implemented a generic write-only Control property (rather than separate properties for text boxes, combo boxes, and check boxes).  

That solution was still far from ideal, as it required us to create a separate instance of our class module for every control in the detail section.  As a reminder, here's the calling code:

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

A Better Way

While there may still be situations where we need to be explicit about which controls should use the modified behavior and which should not, most of the time we want to override the behavior of each control that meets the following criteria:

• Is a textbox, combo box, or check box
• Is enabled
• Is visible
• Is in the detail section

Passing the form object

For most situations, then, we need only pass the form object to our class module.  We can then loop through the controls applying the above criteria and overriding the KeyDown behavior of all qualifying controls.

Let's create a separate public method to handle that situation.  We'll also change the name of the class module from weArrowKeyControl to weArrowKeyNav to reflect the class's updated functionality:

' An excerpt from the newly-renamed weArrowKeyNav class module...

Public Sub FullArrowKeyNav(Frm As Form)
    Dim Ctl As Control
    For Each Ctl In Frm.Section(acDetail).Controls
        Select Case Ctl.ControlType
        Case acTextBox, acComboBox, acCheckBox
            If Ctl.Enabled And Ctl.Visible Then
                'Handle the KeyDown event for this control
            End If
        End Select
    Next Ctl
End Sub

To call the class module from our form code-behind, we now need only two lines of code:

Dim ArrowKeyNav As New weArrowKeyNav

Private Sub Form_Load()
    ArrowKeyNav.FullArrowKeyNav Me.Form
End Sub

Handling the KeyDown Event for each Control

You'll notice I cheated a bit in my sample code above.  I left out the most important part of the FullArrowKeyNav routine: how to handle the KeyDown event for each control.

We need some way to override the behavior of each control.  Let's start there.  

We'll define a local variable whose data type matches this class.  We'll then assign each control within our loop to that variable:

Dim NavCtl As weArrowKeyNav
Set NavCtl = New weArrowKeyNav
Set NavCtl.Control = Ctl

Of course, each time through the loop, the NavCtl object is being reset and the previous value is being lost.  With this code, only the last control in the loop will have our custom behavior.

An internal collection keeps instances in scope

We need some way to preserve each control within its own object and ensure each object does not get garbage collected before the form closes.

The easiest way to do that is to create an internal collection to hold each instance of the weArrowKeyNav class (one for each qualifying control on the calling form).

To do this, we add the following line to the class module's header:

Private CtlColl As Collection  'Of weArrowKeyNav objects

Then we add this line to the control loop:

CtlColl.Add NavCtl

The FullArrowKeyNav Public Subroutine

Here's the final code for the FullArrowKeyNav subroutine:

Public Sub FullArrowKeyNav(Frm As Form)
    Dim Ctl As Control
    Set CtlColl = New Collection
    For Each Ctl In Frm.Section(acDetail).Controls
        Select Case Ctl.ControlType
        Case acTextBox, acComboBox, acCheckBox
            If Ctl.Enabled And Ctl.Visible Then
                Dim NavCtl As weArrowKeyNav
                Set NavCtl = New weArrowKeyNav
                Set NavCtl.Control = Ctl
                CtlColl.Add NavCtl
            End If
        End Select
    Next Ctl
End Sub

Understanding what's going on

To better understand what's happening, let's look at the ArrowKeyNav object in our calling form after the call to FullArrowKeyNav:

In the screenshot above, notice that the three module-level control variables–weCheckBox, weComboBox, and weTextBox–are all set to Nothing.  That's because the ArrowKeyNav object acts as a container for the individual control objects.  Those six controls are shown as weArrowKeyNav items within the CtlColl collection.

Finishing touches yet to come

There are still a couple final details we need to work out before we can put a bow on this weArrowKeyNav class module:

• Handling dropped-down combo boxes
• Avoiding unnecessary incrementing of the autonumber ID

Image by Pete Linforth from Pixabay (fractals are fun!)