VBA – Microsoft Access Navigation Pane Automation Via UIAutomation

A recent question by a fellow MVP made me look at some old code to see if I could solve the issue using VBA UIAutomation.

I’ve briefly touched upon UIAutomation in the past with articles like:

Access - VBA - Compact the Current Database
Here’s a functionality that is often asked for: How can I compact the current database via code (VBA)? Typically the answer is either: It can’t be done (the functionality was removed, don’t ask me why – ask Microsoft?!) Set the Compact on Close property to True, close the database and then set it back to…

Continue reading →

Target
VBA - Activate a Specific Ribbon Tab
As part of my article on Compacting the current database:   Some users indicated an issue that the code only worked when the Database Tools tab was first activated. So I wanted to find a way to do this, and I finally did!
Access - VBA - Set Focus On Specific Document Tab
I was trying to help out in a UA thread:   The Question? The question was: How can one switch to a specific tab, when using Tab Documents?

So What Can UIAutomation Do For Us When It Comes To The Navigation Pane?

Well, it can enable us to do things such as:

So let us explore UIAutomation and the Navigation Pane for a moment!


SHOV And Helper Functions

To simplify and optimize my code, I am using a UIAutomationClient SHOV (Self-Healing Object Variable) and a couple reusable helper functions.

SHOV

Private pUIA As UIAutomationClient.CUIAutomation


Public Function oUIA() As UIAutomationClient.CUIAutomation
    If pUIA Is Nothing Then
        Set pUIA = New UIAutomationClient.CUIAutomation
    End If
    Set oUIA = pUIA
End Function

Public Sub oUIA_Clear()
    'Be sure to always run this when closing your Form/DB to avoid
    '   hidden instances from running in the background!
    Set pUIA = Nothing
End Sub

If you’re not already familiar with SHOV, I’d urge you to look over:

Rocket
Self-Healing Object Variables
So what does a developer do when he can’t sleep at 3am? He decides to write an article about self-healing object variables! So What Are Self-Healing Object Variables? In plain English, these are variables that are intelligent enough that they initialize themselves once and retain their value for the entire session.  If reset, they will…

Continue reading →

Helper Functions

Below are a couple helper functions used with the Navigation Pane functions.

'---------------------------------------------------------------------------------------
' Procedure : UIA_Find_DbElement
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Use to return the IUIAutomationElement of the HWND
'               For our purposes the current database
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: UIAutomationClient
' Dependencies: oUIA()
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' Hwnd      : The HWND of the element we want to bind to
'
' Usage:
' ~~~~~~
' Set oUIAAccess = UIA_Find_DbElement(Application.hWndAccessApp)
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-07-06              Dev
' 2         2022-10-22              Initial Release
'---------------------------------------------------------------------------------------
Public Function UIA_Find_DbElement(ByVal Hwnd As Long) As UIAutomationClient.IUIAutomationElement
On Error GoTo Error_Handler
'    Dim oUIA                 As UIAutomationClient.CUIAutomation '.IUIAutomation6 .IUIAutomation
'
'    Set oUIA = New UIAutomationClient.CUIAutomation
    Set UIA_Find_DbElement = oUIA.ElementFromHandle(ByVal Hwnd)

Error_Handler_Exit:
    On Error Resume Next
'    Set oUIA = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: UIA_Find_DbElement" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

'---------------------------------------------------------------------------------------
' Procedure : UIA_FindElement
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Retrieve the matching element in the subtree of the passed element
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: UIAutomationClient
' Dependencies: oUIA()
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' Application   : Element to search the subtree of
' Name          : Name of the element being searched for
'
' Usage:
' ~~~~~~
' Set oUIAElement = UIA_FindElement(UIA_Find_DbElement(Application.hWndAccessApp), "Clear Search String")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-07-06              Dev
' 2         2022-10-22              Initial Release
'---------------------------------------------------------------------------------------
Public Function UIA_FindElement(ByRef Application As UIAutomationClient.IUIAutomationElement, _
                                ByVal Name As String) As UIAutomationClient.IUIAutomationElement
On Error GoTo Error_Handler
'    Dim oUIA                  As UIAutomationClient.CUIAutomation
    Dim oUIACondition         As UIAutomationClient.IUIAutomationCondition
    Dim oUIAElement           As UIAutomationClient.IUIAutomationElement

'    Set oUIA = New UIAutomationClient.CUIAutomation
    Set oUIACondition = oUIA.CreatePropertyCondition(UIA_NamePropertyId, Name)
    Set oUIAElement = Application.FindFirst(TreeScope_Subtree, oUIACondition)
    Set UIA_FindElement = oUIAElement

Error_Handler_Exit:
    On Error Resume Next
    Set oUIAElement = Nothing
    Set oUIACondition = Nothing
'    Set oUIA = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: UIA_FindElement" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

'---------------------------------------------------------------------------------------
' Procedure : UIA_FindElement_NameAndClass
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Retrieve the matching element name with the specified class in the subtree
'               of the passed element
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: UIAutomationClient
' Dependencies: oUIA()
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' Application   : Element to search the subtree of
' sName         : Name of the element being searched for
' sClass        : Name of the Class to match
'
' Usage:
' ~~~~~~
' Set oUIAElement = UIA_FindElement_NameAndClass(UIA_Find_DbElement(Application.hWndAccessApp), _
'                                                "Clear Search String", "NetUINavPaneGroup")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-10-25              Initial Release
'---------------------------------------------------------------------------------------
Public Function UIA_FindElement_NameAndClass(ByRef Application As IUIAutomationElement, _
                                             ByVal sName As String, _
                                             ByVal sClass As String) As IUIAutomationElement
    Dim UIA                   As UIAutomationClient.CUIAutomation
    Dim Condition             As UIAutomationClient.IUIAutomationCondition
    Dim element               As UIAutomationClient.IUIAutomationElement

    Set UIA = New CUIAutomation
    Set Condition = UIA.CreateAndCondition(UIA.CreatePropertyCondition(UIA_NamePropertyId, sName), _
                                           UIA.CreatePropertyCondition(UIA_ClassNamePropertyId, sClass))
    Set element = Application.FindFirst(TreeScope_Subtree, Condition)

    Set UIA_FindElement_NameAndClass = element

    Set element = Nothing
    Set Condition = Nothing
    Set UIA = Nothing
End Function

'---------------------------------------------------------------------------------------
' Procedure : UIA_Child_Class
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Retrieve the matching element name with the specified class in the subtree
'               of the passed element
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: UIAutomationClient
' Dependencies: oUIA()
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' oParentElement: Parent Element to search the children of
' sClass        : Name of the Class to match
'
' Usage:
' ~~~~~~
' Set oUIAElement = UIA_Child_Class(UIA_Find_DbElement(Application.hWndAccessApp), _
'                                   "NetUINavPaneGroup")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-10-26              Initial Release
'---------------------------------------------------------------------------------------
Public Function UIA_Child_Class(ByRef oParentElement As IUIAutomationElement, _
                                ByVal sClass As String) As IUIAutomationElement
'    Dim oUIA                   As UIAutomationClient.CUIAutomation
    Dim Condition             As UIAutomationClient.IUIAutomationCondition
    Dim oElement               As UIAutomationClient.IUIAutomationElement

'    Set oUIA = New CUIAutomation
    Set Condition = oUIA.CreatePropertyCondition(UIA_ClassNamePropertyId, sClass)
    Set oElement = oParentElement.FindFirst(TreeScope_Children, Condition)

    Set UIA_Child_Class = oElement

    Set oElement = Nothing
    Set Condition = Nothing
'    Set oUIA = Nothing
End Function
Late Binding
Shane Groff, of the Microsoft Access Dev Team, was kind enough to explain to me that it is not possible to use Late Binding with the UIAutomationClient because IUIAutomation does not support the IDispatch interface.

Now that we have our SHOV and a few helper functions, we can build upon them to work with and automate the Navigation Pane.

Toggling the Navigation Pane

We can Toggle the Navigation Pane by doing:

'---------------------------------------------------------------------------------------
' Procedure : UIA_NavPane_ExpandToggle
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Shrinks/Expands the Navigation Pane
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: UIAutomationClient
' Dependencies: UIA_Find_DbElement & UIA_FindElement
'
' Usage:
' ~~~~~~
' Call UIA_NavPane_ExpandToggle
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-07-06              Dev
' 2         2022-10-22              Initial Release
'---------------------------------------------------------------------------------------
Public Function UIA_NavPane_ExpandToggle()
On Error GoTo Error_Handler
    Dim oUIAAccess                As UIAutomationClient.IUIAutomationElement
    Dim oUIAElement               As UIAutomationClient.IUIAutomationElement
    Dim oUIAInvokePattern         As UIAutomationClient.IUIAutomationInvokePattern

    Set oUIAAccess = UIA_Find_DbElement(Application.hWndAccessApp)
    If Not (oUIAAccess Is Nothing) Then

        Set oUIAElement = UIA_FindElement(oUIAAccess, "Shutter Bar Open/Close Button")
        If Not (oUIAElement Is Nothing) Then
            oUIAElement.SetFocus

            Set oUIAInvokePattern = oUIAElement.GetCurrentPattern(UIA_InvokePatternId)
            oUIAInvokePattern.Invoke
        End If
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set oUIAInvokePattern = Nothing
    Set oUIAElement = Nothing
    Set oUIAAccess = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: UIA_NavPane_ExpandToggle" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

and then we simple call it like:

Call UIA_NavPane_ExpandToggle

Expand/Shrink Navigation Pane

The Toggle is interesting, but probably not truly useful as we probably want to set a specific state, not toggle blindly and that is what the following procedure does!

'---------------------------------------------------------------------------------------
' Procedure : UIA_NavPane_Expand
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Expand or Shrinks the Navigation Pane
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: UIAutomationClient
' Dependencies: UIA_Find_DbElement, UIA_FindElement_NameAndClass & UIA_Child_Class
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' bExpand   : Whether to Shrink or Expand
'               True => Expand, False => Shrink
'
' Usage:
' ~~~~~~
' Call UIA_NavPane_Expand        'Expand
' Call UIA_NavPane_Expand(False) 'Shrink
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-07-06              Dev
' 2         2022-10-22              Initial Release
' 3         2022-10-26              Updated to not rely on the width dimension comparison
'                                   wasn't sure if that was dependable
'---------------------------------------------------------------------------------------
Public Function UIA_NavPane_Expand(Optional bExpand As Boolean = True)
    On Error GoTo Error_Handler
    Dim oUIAAccessDB          As UIAutomationClient.IUIAutomationElement
    Dim oUIAElementNPH        As UIAutomationClient.IUIAutomationElement
    Dim oUIAElementNP         As UIAutomationClient.IUIAutomationElement
    Dim oUIAElementGBtn       As UIAutomationClient.IUIAutomationElement
    Dim oUIAElementSBtn       As UIAutomationClient.IUIAutomationElement
    Dim oUIAInvokePattern     As UIAutomationClient.IUIAutomationInvokePattern
    Dim lNavPaneIsOffscreen   As Long

    Set oUIAAccessDB = UIA_Find_DbElement(Application.hWndAccessApp)
    If Not (oUIAAccessDB Is Nothing) Then

        Set oUIAElementNPH = UIA_FindElement_NameAndClass(oUIAAccessDB, "Navigation Pane Host", "NetUINativeHWNDHost")
        If Not (oUIAElementNPH Is Nothing) Then

            Set oUIAElementNP = UIA_FindElement_NameAndClass(oUIAElementNPH, "Navigation Pane", "NetUINetUI")

            Set oUIAElementGBtn = UIA_Child_Class(oUIAElementNP, "NetUIGroupFilterButton")
            If oUIAElementGBtn Is Nothing Then
                lNavPaneIsOffscreen = 1
            Else
                lNavPaneIsOffscreen = oUIAElementGBtn.CurrentIsOffscreen
            End If

            If (bExpand = True And lNavPaneIsOffscreen <> 0) Or (bExpand = False And lNavPaneIsOffscreen <> 1) Then
                Set oUIAElementSBtn = UIA_FindElement_NameAndClass(oUIAElementNPH, "Shutter Bar Open/Close Button", "NetUIButton")
                If Not (oUIAElementSBtn Is Nothing) Then
                    Set oUIAInvokePattern = oUIAElementSBtn.GetCurrentPattern(UIA_InvokePatternId)
                    oUIAInvokePattern.Invoke
                End If
            End If
        End If
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set oUIAInvokePattern = Nothing
    Set oUIAElementSBtn = Nothing
    Set oUIAElementGBtn = Nothing
    Set oUIAElementNP = Nothing
    Set oUIAElementNPH = Nothing
    Set oUIAAccessDB = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: UIA_NavPane_Expand" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

To Expand the Navigation Pane you simply do:

Call UIA_NavPane_Expand

Or

Call UIA_NavPane_Expand(True)

and to Shrink the Navigation Pane you simply do:

Call UIA_NavPane_Expand(False)

Perform a Search In The Navigation Pane Search Bar

Performing A Search

The I thought to myself that it would be fun if we could actually automate searches and so I developed the following function

'---------------------------------------------------------------------------------------
' Procedure : UIA_NavPane_Search
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Apply a search to the Navigation Pane
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: UIAutomationClient
' Dependencies: UIA_Find_DbElement & UIA_FindElement
'               The Navigation Pane's Search Bar must to be visible for this to work
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTerm     : Term to input into the Search control and search form
'
' Usage:
' ~~~~~~
' Call UIA_NavPane_Search("Menu")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-07-06              Dev
' 2         2022-10-22              Initial Release
'---------------------------------------------------------------------------------------
Public Function UIA_NavPane_Search(sTerm As String)
On Error GoTo Error_Handler
    Dim oUIAAccess                      As UIAutomationClient.IUIAutomationElement
    Dim oUIAElement                     As UIAutomationClient.IUIAutomationElement
    Dim oUIALegacyIAccessiblePattern    As UIAutomationClient.IUIAutomationLegacyIAccessiblePattern

    Set oUIAAccess = UIA_Find_DbElement(Application.hWndAccessApp)
    If Not (oUIAAccess Is Nothing) Then

        Set oUIAElement = UIA_FindElement(oUIAAccess, "Search...")
        If Not (oUIAElement Is Nothing) Then
            oUIAElement.SetFocus

            Set oUIALegacyIAccessiblePattern = oUIAElement.GetCurrentPattern(UIA_LegacyIAccessiblePatternId)
            oUIALegacyIAccessiblePattern.SetValue (sTerm)
        Else
            Debug.Print "Navigation Pane's Search Bar is not currently accessible."
            'Maybe notify the user here?
        End If
    End If
 
Error_Handler_Exit:
    On Error Resume Next
    Set oUIALegacyIAccessiblePattern = Nothing
    Set oUIAElement = Nothing
    Set oUIAAccess = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: UIA_NavPane_Search" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

We can then perform a search by doing:

Call UIA_NavPane_Search("Menu")

Clearing The Search Bar

Lastly, I wanted a way to reset/clear the Search Bar, and so I created the following:

'---------------------------------------------------------------------------------------
' Procedure : UIA_NavPane_Clear
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Clears the Navigation Pane's Search control
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: UIAutomationClient
' Dependencies: UIA_Find_DbElement & UIA_FindElement
'
' Usage:
' ~~~~~~
' Call NavPane_Clear
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2022-07-06              Dev
' 2         2022-10-22              Initial Release
'---------------------------------------------------------------------------------------
Public Function UIA_NavPane_Clear()
On Error GoTo Error_Handler
    Dim oUIAAccess                As UIAutomationClient.IUIAutomationElement
    Dim oUIAElement               As UIAutomationClient.IUIAutomationElement
    Dim oUIAInvokePattern         As UIAutomationClient.IUIAutomationInvokePattern

    Set oUIAAccess = UIA_Find_DbElement(Application.hWndAccessApp)
    If Not (oUIAAccess Is Nothing) Then

        Set oUIAElement = UIA_FindElement(oUIAAccess, "Clear Search String")
        If Not (oUIAElement Is Nothing) Then
            oUIAElement.SetFocus

            Set oUIAInvokePattern = oUIAElement.GetCurrentPattern(UIA_InvokePatternId)
            oUIAInvokePattern.Invoke
        End If
    End If
     
Error_Handler_Exit:
    On Error Resume Next
    Set oUIAInvokePattern = Nothing
    Set oUIAElement = Nothing
    Set oUIAAccess = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: UIA_NavPane_Clear" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

which you can call by doing:

Call NavPane_Clear

Final Remarks

As you can see, once again, UIAutomation can open some interesting programming avenues for working with and manipulating the GUI.

I will say it has been hard to find a lot of useful VBA-centric information on the subject, so it does take a lot of trial and error to figure things out.

Also, just because a method/property exists does not necessarily mean it has been exposed through VBA for us to use. So some of the things I wanted to do, to date, have been impossible (well with this technique at least).

Resources on the Subject

UI Automation Client Programmer’s Guide - Win32 apps
This section contains information about creating applications that use Microsoft UI Automation to interact with the UI of other applications and the desktop.

5 responses on “VBA – Microsoft Access Navigation Pane Automation Via UIAutomation”

  1. Mohsin Zia Malik

    Hi Daniel,

    Great piece of information, thank you very much. Looking forward to the Youtube video 🙂
    One question regarding the old article of ‘compact and repair database’, I have custom ribbon controls for the client, is there any hint to ‘compact and repair’ through ‘File menu’ and then click ‘compact and repair’ under ‘info’ tab?

    Best,
    Mohsin

  2. Kim Young

    Thank you for this article! With users having bigger monitors and access having a form size limit of 22, I have been implementing use of the Navigation Pane more. It’s easier than a ribbon to create. I have tried using these commands which don’t work.
    CurrentDb.Properties(“NavPane Closed”) = False ‘Make sure it’s open
    CurrentDb.Properties(“NavPane Width”) = 300 ‘Set the width
    DoCmd.NavigateTo NavPaneCategory ‘Go to our custom Category

    This will be a huge help! Thank you again!

    1. Daniel Pineault Post author

      I mention it in my video, but the Navigation Pane properties have no effect. You can read them fine, but changes do not get applied. I reached out to the Dev Team a week or 2 ago and sadly never heard back on the subject.