I recently published one of my YouTube “Quick Tip” videos on how we can easily highlight the active control using conditional formatting, so no code required!

I quickly received a comment about wanting to emulate what is done is Web Apps, that is to change the border rather than the background itself. So I thought I’d explore that briefly.

The issue here is that Conditional Formatting is VERY limited! We only can alter:

• Bold
• Italic
• Underline
• Background color
• Font color
• Enable/disable the control itself

Sadly, we can’t control

• Borders
• Back styles
• Font: Name, Size
• etc.

So where does that leave us?  VBA!  Good old VBA.

Below, I will present 2 similar approaches to emulating the Web App changing the border of a control when it has the focus, one using plain vanilla VBA and one using a Class.  These are designed for use in Single Form usage.
 

Why Do This?

Some might ask why do such a thing, why differentiate the active control in this manner. It is all about improving the user experience! We want to make it easy for the user to find themselves on the form, especially complex forms! This is just one more very simple way of doing so.

For instance, if I add this to Microsoft Contacts Application Parts example, we could end up with something like:

As you can see by the image above, there is no doubt as to which control, where the cursor, is currently on the form!

It’s always about the user experience!!!
 

Plain Vanilla VBA Approach

If we want to use plain vanilla VBA, we need only add a little code to the form’s On Open event to set each control’s On Got Focus and On Lost Focus event to call a couple public functions.

So the first thing we need is to create a new Standard Module and insert:

Public Function AddBorder(sFrmName As String, sCtrlName As String)
    Dim ctrl As Access.Control
    
    Set ctrl = Forms(sFrmName).Form.Controls(sCtrlName)
    ctrl.BorderColor = RGB(255, 127, 0)
    ctrl.BorderWidth = 2
    Set ctrl = Nothing
End Function

Public Function RemoveBorder(sFrmName As String, sCtrlName As String)
    Dim ctrl As Access.Control
    
    Set ctrl = Forms(sFrmName).Form.Controls(sCtrlName)
    ctrl.BorderColor = RGB(0, 0, 0)
    ctrl.BorderWidth = 1
    Set ctrl = Nothing
End Function

Next, we then add an On Open event to our form similar to:

Private Sub Form_Open(Cancel As Integer)
    Dim ctrl As Access.Control
    
    For Each ctrl In Me.Controls
        Select Case ctrl.ControlType
        Case acTextBox, acComboBox, acListBox
            ctrl.OnGotFocus = "=AddBorder(""" & Me.Name & """, """ & ctrl.Name & """)"
            Debug.Print "=AddBorder(""" & Me.Name & """, """ & ctrl.Name & """)"
            ctrl.OnLostFocus = "=RemoveBorder(""" & Me.Name & """, """ & ctrl.Name & """)"
        End Select
    Next
    
    Set ctrl = Nothing
End Sub

And that’s it! The next time you open you form, the specified control types in the Case statement will have their border altered to orange when the control gets the focus.
 

Class Module Approach

Another option would be to use a Class module. Below is a very simple example.

per our previous example, we are going to start by creating a new Standard Module and insert:

Public Function AddBorder(sFrmName As String, sCtrlName As String)
    Dim ctrl As Access.Control
    
    Set ctrl = Forms(sFrmName).Form.Controls(sCtrlName)
    ctrl.BorderColor = RGB(255, 127, 0)
    ctrl.BorderWidth = 2
    Set ctrl = Nothing
End Function

Public Function RemoveBorder(sFrmName As String, sCtrlName As String)
    Dim ctrl As Access.Control
    
    Set ctrl = Forms(sFrmName).Form.Controls(sCtrlName)
    ctrl.BorderColor = RGB(0, 0, 0)
    ctrl.BorderWidth = 1
    Set ctrl = Nothing
End Function

Next we create a new Class Module and name it cls_ActiveControlBehavior:

Private m_frm As Access.Form


Public Property Get p_frm() As Access.Form
    Set p_frm = m_frm
End Property

Public Property Set p_frm(ByRef oFrm As Access.Form)
    Set m_frm = oFrm
    Call Class_init
End Property

Private Sub Class_init()
    Dim ctrl As Control
    
    For Each ctrl In m_frm.Controls
        Select Case ctrl.ControlType
            Case acTextBox, acComboBox, acListBox
                ctrl.OnGotFocus = "=AddBorder(""" & m_frm.Name & """, """ & ctrl.Name & """)"
                ctrl.OnLostFocus = "=RemoveBorder(""" & m_frm.Name & """, """ & ctrl.Name & """)"
        End Select
    Next
    Set ctrl = Nothing
End Sub

Lastly, for any for we wish to apply this to, we add:

Dim listener As New cls_ActiveControlBehavior


Private Sub Form_Close()
    Set listener = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
    Set listener.p_frm = Me
End Sub

Although the Class initially appears to be more complex, the benefit is that you can apply it to a multitude of forms and then make change in the class which will apply to all the forms in one shot whereas the Plain Vanilla VBA approach would require you updating each form’s code separately.

Class Module Approach – To The Max

If we wanted to, we could take things to the extreme with Class modules, and to illustrate a little how we can use Class Module to implement event procedures on form control’s here’s a quick example.

First we need to create 5 Class modules (the code for each is provided below):

• Cls_Controls
• Cls_Controls_ComboBox
• Cls_Controls_CommandButton
• Cls_Controls_ListBox
• Cls_Controls_TextBox

Cls_Controls

Option Compare Database
Option Explicit

Private collCtrls             As VBA.Collection
Private oForm                 As Access.Form
Private oComboBox             As Cls_Controls_ComboBox
Private oCommandButton        As Cls_Controls_CommandButton
Private oListBox              As Cls_Controls_ListBox
Private oTextBox              As Cls_Controls_TextBox


Public Property Get p_Form() As Access.Form
    Set p_Form = oForm
End Property

Public Property Set p_Form(ByRef oMyForm As Access.Form)
    Set oForm = oMyForm
    Class_init
End Property

Private Sub Class_init()
    Dim ctrl                  As Access.Control
    Const EVNTPROC = "[Event Procedure]"

    Set collCtrls = New Collection
    For Each ctrl In oForm.Controls
        Select Case ctrl.ControlType
            Case acComboBox
                Set oComboBox = New Cls_Controls_ComboBox
                Set oComboBox.p_ComboBox = ctrl
                oComboBox.p_ComboBox.OnGotFocus = EVNTPROC
                oComboBox.p_ComboBox.OnLostFocus = EVNTPROC
                collCtrls.Add oComboBox
                Set oComboBox = Nothing
            Case acCommandButton
                Set oCommandButton = New Cls_Controls_CommandButton
                Set oCommandButton.p_CommandButton = ctrl
                oCommandButton.p_CommandButton.OnGotFocus = EVNTPROC
                oCommandButton.p_CommandButton.OnLostFocus = EVNTPROC
                collCtrls.Add oCommandButton
                Set oCommandButton = Nothing
            Case acListBox
                Set oListBox = New Cls_Controls_ListBox
                Set oListBox.p_ListBox = ctrl
                oListBox.p_ListBox.OnGotFocus = EVNTPROC
                oListBox.p_ListBox.OnLostFocus = EVNTPROC
                collCtrls.Add oListBox
                Set oListBox = Nothing
            Case acTextBox
                Set oTextBox = New Cls_Controls_TextBox
                Set oTextBox.p_TextBox = ctrl
                oTextBox.p_TextBox.OnGotFocus = EVNTPROC
                oTextBox.p_TextBox.OnLostFocus = EVNTPROC
                collCtrls.Add oTextBox
                Set oTextBox = Nothing
        End Select
    Next

    Set ctrl = Nothing
End Sub

Private Sub Class_Terminate()
    Set collCtrls = Nothing
End Sub

Cls_Controls_ComboBox

Option Compare Database
Option Explicit

Private WithEvents m_ComboBox As Access.ComboBox


Public Property Get p_ComboBox() As Access.ComboBox
    Set p_ComboBox = m_ComboBox
End Property

Public Property Set p_ComboBox(ByRef oComboBox As Access.ComboBox)
    Set m_ComboBox = oComboBox
End Property

Private Sub m_ComboBox_GotFocus()
    m_ComboBox.BorderColor = RGB(255, 127, 0)
    m_ComboBox.BorderWidth = 2
End Sub

Private Sub m_ComboBox_LostFocus()
    m_ComboBox.BorderColor = RGB(0, 0, 0)
    m_ComboBox.BorderWidth = 1
End Sub

Cls_Controls_CommandButton

Option Compare Database
Option Explicit

Private WithEvents m_CommandButton As Access.CommandButton


Public Property Get p_CommandButton() As Access.CommandButton
    Set p_CommandButton = m_CommandButton
End Property

Public Property Set p_CommandButton(oCommandButton As Access.CommandButton)
    Set m_CommandButton = oCommandButton
End Property

Private Sub m_CommandButton_GotFocus()
    m_CommandButton.BorderColor = RGB(255, 127, 0)
    m_CommandButton.BorderWidth = 2
End Sub

Private Sub m_CommandButton_LostFocus()
    m_CommandButton.BorderColor = RGB(0, 0, 0)
    m_CommandButton.BorderWidth = 1
End Sub

Cls_Controls_ListBox

Option Compare Database
Option Explicit

Private WithEvents m_ListBox  As Access.ListBox


Public Property Get p_ListBox() As Access.ListBox
    Set p_ListBox = m_ListBox
End Property

Public Property Set p_ListBox(ByRef oListBox As Access.ListBox)
    Set m_ListBox = oListBox
End Property

Private Sub m_ListBox_GotFocus()
    m_ListBox.BorderColor = RGB(255, 127, 0)
    m_ListBox.BorderWidth = 2
End Sub

Private Sub m_ListBox_LostFocus()
    m_ListBox.BorderColor = RGB(0, 0, 0)
    m_ListBox.BorderWidth = 1
End Sub

Cls_Controls_TextBox

Option Compare Database
Option Explicit

Private WithEvents m_TextBox  As Access.TextBox


Public Property Get p_TextBox() As Access.TextBox
    Set p_TextBox = m_TextBox
End Property

Public Property Set p_TextBox(ByRef oTextBox As Access.TextBox)
    Set m_TextBox = oTextBox
End Property

Private Sub m_TextBox_GotFocus()
    m_TextBox.BorderColor = RGB(255, 127, 0)
    m_TextBox.BorderWidth = 2
End Sub

Private Sub m_TextBox_LostFocus()
    m_TextBox.BorderColor = RGB(0, 0, 0)
    m_TextBox.BorderWidth = 1
End Sub

Then, in our form, we only need:

Private listener              As New Cls_Controls


Private Sub Form_Load()
    Set listener.p_Form = Me
End Sub

Private Sub Form_Close()
    Set listener = Nothing
End Sub

I know this seems to be much more complicated, but in reality it give you granularity on each control type. This is a great demonstration of Class modules for setting up forms, control event from a central Class location. We could create a Global Var or TempVar to hold the colors so things could be controlled from a single variable, instead of hard coded in each Class as is the case above, Or even read from a table so the user could specify the color they wish to have displayed. Lots of possibilities.

Now, I’ve set things up for Combo boxes, Command buttons, List boxes and Text boxes, but the same pattern can be extended to include other controls if you wish to.
 

Customizations

You can change the border properties by changing the .BorderColor = RGB() or .BorderWidth values in the Standard Module public functions.

Furthermore, you can customize the Case statement in either approach to include, or exclude, whatever controls you wish to.
 

Other Possibilities

Now obviously, in my examples I change the border properties, but you can alter any property you want via either of these approaches as they offer all that VBA has to offer!  You could change the font, backs tyle, alter the associated label look and feel, …  The sky’s the limit here!  So have fun.