CenterForm(): Using the Windows API to Center Forms on the Access Canvas

The form Auto-Center property is all you need in most cases. However, let's consider four different situations where my custom code might be a better fit.

CenterForm(): Using the Windows API to Center Forms on the Access Canvas

As you likely know, Microsoft Access forms have an Auto-Center property.  And if you don't know about the Auto-Center property, go check out Richard Rost's introductory video on the topic on YouTube.

If all you are looking to do is center a form on-screen when it first opens, then setting that property to True will be much easier and cleaner than using this approach.  

However, there are a few reasons why you might still want to use my CenterForm() procedure in your application:

  • You want your form to appear in the dead center of the Access canvas, not slightly above center as the Auto-Center property does
  • You want to re-center a form after the application window has moved or resized without having to close and reopen the form
  • You want to center a form other than the active form
  • You want to use this as a starting point to experiment with arbitrarily positioning Access forms and reports (though my Fun With Form Windows article might be a better place to start for that)

The CenterForm() procedure is a great little introduction into using the Windows API to play around with form (and report) positioning in Access.

The Approach

The CenterForm procedure leverages the Windows API to interact with the operating system at a low level, fetching window dimensions and positioning forms with precision. The procedure can be called with a specific form's window handle or, if none is provided, it defaults to centering the currently active form.

The Algorithm

The CenterForm procedure operates through the following steps:

  1. Determine if a form's window handle is provided; if not, use the active form's handle.
  2. Obtain the window handle of the Access application's client area (the canvas).
  3. Retrieve the dimensions of the Access canvas and the form using the apiGetWindowRect function.
  4. Calculate the center of the Access canvas.
  5. Check the form's size and adjust it if it's larger than the canvas, ensuring it fits within the Access window.
  6. Center the form by calculating the new top-left position based on the canvas center and form size.
  7. Apply the new size and position to the form using the apiMoveWindow function.

The Function

Here is the CenterForm() function on its own without its required dependencies:

Sub CenterForm(Optional ByVal FormHWnd As LongPtr = 0)
    'If calling code does not provide a form window handle,
    '   get the window handle of the active form
    If FormHWnd = 0 Then FormHWnd = Screen.ActiveForm.hWnd
    
    'Get the window handle of the Access canvas
    Dim InnerAccessHWnd As LongPtr
    InnerAccessHWnd = GetInnerAccessHwnd(FormHWnd)
    
    'Get the canvas rectangle
    Dim CanvasRect As Rect
    apiGetWindowRect InnerAccessHWnd, CanvasRect
    
    'Get the form's rectangle
    Dim FrmRect As Rect
    apiGetWindowRect FormHWnd, FrmRect
    
    'Calculate the canvas's width and height
    Dim CanvasWidth As Long, CanvasHeight As Long
    CanvasWidth = CanvasRect.Right - CanvasRect.Left
    CanvasHeight = CanvasRect.Bottom - CanvasRect.Top
    
    'Calculate the horizontal and vertical center of the canvas
    Dim hCanvasCenter As Long, vCanvasCenter As Long
    hCanvasCenter = CanvasWidth \ 2
    vCanvasCenter = CanvasHeight \ 2
    
    'Calculate the form's width and height, resizing it
    '   if needed to use all available width/height
    '   if form is wider or taller than the canvas
    Dim FrmWidth As Long, FrmHeight As Long
    FrmWidth = FrmRect.Right - FrmRect.Left
    If FrmWidth > CanvasWidth Then FrmWidth = CanvasWidth
    
    FrmHeight = FrmRect.Bottom - FrmRect.Top
    If FrmHeight > CanvasHeight Then FrmHeight = CanvasHeight
    
    'Use Windows API to set the size and position of the form
    '   so that it is perfectly centered vertically and horizontally
    '   on the Access canvas
    apiMoveWindow FormHWnd, (hCanvasCenter - (FrmWidth \ 2)), _
                            (vCanvasCenter - (FrmHeight \ 2)), _
                            FrmWidth, FrmHeight, True
End Sub

The Full Code

The code below includes required helper functions that I've written about in the past:

Option Compare Database
Option Explicit

Private Declare PtrSafe Function apiFindWindowEx _
    Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, _
                                        ByVal hWnd2 As LongPtr, _
                                        ByVal lpsz1 As String, _
                                        ByVal lpsz2 As String) As LongPtr

Private Declare PtrSafe Function apiGetParent _
    Lib "user32" Alias "GetParent" (ByVal hWnd As LongPtr) As LongPtr

Private Declare PtrSafe Function apiGetWindowRect _
    Lib "user32" Alias "GetWindowRect" (ByVal hWnd As LongPtr, _
                                        lpRect As Rect) As Long

Private Declare PtrSafe Function apiMoveWindow _
    Lib "user32" Alias "MoveWindow" (ByVal hWnd As LongPtr, _
                                     ByVal X As Long, ByVal Y As Long, _
                                     ByVal nWidth As Long, ByVal nHeight As Long, _
                                     ByVal bRepaint As Long) As Long

Private Type Rect
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

'---------------------------------------------------------------------------------------
' Procedure : GetInnerAccessHwnd
' Date      : 11/26/2014 - 12/12/2023
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/getinneraccesshwnd/
' Purpose   : Gets the window handle of the Access "canvas".
' Notes     - We don't use hWndAccessApp here because that's the window handle for the
'               full application; what we really want is the handle for the "inner container";
'               i.e., the Access window minus the docked menus, docked toolbars, and status bar
'---------------------------------------------------------------------------------------
'
Function GetInnerAccessHwnd(Optional ByVal ChildHWnd As LongPtr = 0) As LongPtr
    
    GetInnerAccessHwnd = apiFindWindowEx(hWndAccessApp, ByVal 0&, "MDIClient", vbNullString)
    If GetInnerAccessHwnd <> 0 Then Exit Function

    On Error Resume Next
    If ChildHWnd = 0 Then ChildHWnd = Screen.ActiveForm.hWnd
    If ChildHWnd = 0 Then ChildHWnd = Screen.ActiveReport.hWnd
    If ChildHWnd = 0 Then ChildHWnd = Screen.ActiveDatasheet.hWnd
    GetInnerAccessHwnd = apiGetParent(ChildHWnd)
End Function


' ----------------------------------------------------------------
' Procedure : CenterForm
' Date      : 7/20/2011 - 12/14/2023
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/centerform/
' Purpose   : Centers a form within the Access "canvas".
' Notes     - Popup forms are positioned absolutely with respect
'               to the screen and not the application window. If the
'               Access window is maximized or placed in the upper-left
'               corner of the screen, this does not matter.  However, if
'               the application is to the lower-right of the screen, then
'               calling CenterForm() on a popup form will cause the form
'               to appear to the left and up from where it should be
'               in relation the Access canvas.
' ----------------------------------------------------------------
Sub CenterForm(Optional ByVal FormHWnd As LongPtr = 0)
    'If calling code does not provide a form window handle,
    '   get the window handle of the active form
    If FormHWnd = 0 Then FormHWnd = Screen.ActiveForm.hWnd
    
    'Get the window handle of the Access canvas
    Dim InnerAccessHWnd As LongPtr
    InnerAccessHWnd = GetInnerAccessHwnd(FormHWnd)
    
    'Get the canvas rectangle
    Dim CanvasRect As Rect
    apiGetWindowRect InnerAccessHWnd, CanvasRect
    
    'Get the form's rectangle
    Dim FrmRect As Rect
    apiGetWindowRect FormHWnd, FrmRect
    
    'Calculate the canvas's width and height
    Dim CanvasWidth As Long, CanvasHeight As Long
    CanvasWidth = CanvasRect.Right - CanvasRect.Left
    CanvasHeight = CanvasRect.Bottom - CanvasRect.Top
    
    'Calculate the horizontal and vertical center of the canvas
    Dim hCanvasCenter As Long, vCanvasCenter As Long
    hCanvasCenter = CanvasWidth \ 2
    vCanvasCenter = CanvasHeight \ 2
    
    'Calculate the form's width and height, resizing it
    '   if needed to use all available width/height
    '   if form is wider or taller than the canvas
    Dim FrmWidth As Long, FrmHeight As Long
    FrmWidth = FrmRect.Right - FrmRect.Left
    If FrmWidth > CanvasWidth Then FrmWidth = CanvasWidth
    
    FrmHeight = FrmRect.Bottom - FrmRect.Top
    If FrmHeight > CanvasHeight Then FrmHeight = CanvasHeight
    
    'Use Windows API to set the size and position of the form
    '   so that it is perfectly centered vertically and horizontally
    '   on the Access canvas
    apiMoveWindow FormHWnd, (hCanvasCenter - (FrmWidth \ 2)), _
                            (vCanvasCenter - (FrmHeight \ 2)), _
                            FrmWidth, FrmHeight, True
End Sub

The code can be copied and pasted into a blank standard module for a fully-working solution that can be easily integrated into your projects.

Sample Usage

Imagine a scenario in a business application where you have a form named "frmEmployeeDetails" that is frequently accessed. To enhance the user experience, you want this form to appear centered within the Access window each time it is opened. Here's how you could implement the CenterForm procedure in the form's Open event:

Private Sub Form_Open(Cancel As Integer)
    ' Center the currently active form
    CenterForm
End Sub

Alternatively, if you need to center a form from another part of your application, you could do so by passing the form's hWnd property to the CenterForm procedure:

Private Sub SomeOtherProcedure()
    ' Assume frmEmployeeDetails is a form object
    Dim Frm As Form
    Set Frm = Forms("frmEmployeeDetails")
    
    ' Center frmEmployeeDetails by passing its hWnd property
    CenterForm Frm.hWnd
End Sub

This approach ensures that your forms are always neatly presented, contributing to a clean and professional interface that your users will appreciate.

A Note About Popup Forms

Popup forms (those whose "Popup" property is set to True) float above the Access application.  Unlike other types of Access forms, they can be moved anywhere on your screen–including outside the bounds of the Access application itself.

In its current state, the CenterForm() function does not include special handling for popup forms.  In practice, this means that the positioning for a popup form is calculated relative to the Access application window, but the form is actually positioned relative to the screen.

If the Access application is maximized or positioned in the very top-left corner of the screen, then everything behaves as you would expect.  

However, if the Access application appears down and to the right of the screen, then the popup form will appear too far up and to the left of where you would otherwise expect to see it.

Using the apiGetParent() function with a normal form will return the window handle (hWnd) to the Access canvas (i.e., the same result as GetInnerAccessHwnd()).  However, if you pass the window handle of a popup form to the apiGetParent() function then it will return the window handle to the Access application itself (i.e., hWndAccessApp).  

Armed with this information, one could find the top left corner of the Access application (via some other Windows API) and add those values to the top and left values when positioning a popup form.  I will leave that improvement as an exercise for the reader (at least for now).

Acknowledgements
  • Portions of this article's body generated with the help of ChatGPT
  • One or more code samples generated with the help of ChatGPT
  • Cover image generated by DALL-E-3

UPDATE [2023-12-14]: Changed FormHWnd and InnerAccessHwnd from Long to LongPtr in CenterForm() to fix compile error when running in 64-bit VBA.

All original code samples by Mike Wolfe are licensed under CC BY 4.0