Form Design

Modern On/Off Button in Access

Leave those boring checkboxes behind and move into the 21st century with these modern on-off switches for Microsoft Access. Very little code required!

Mike Wolfe

Jul 26, 2022 • 5 min read

A couple of months ago, Colin Riddington wrote an article where he demonstrated how to recreate modern Windows-settings-style toggle switches in Access.

Here's a screenshot from Windows Settings to demonstrate the style Colin was trying to emulate:

And here's the fruit of Colin's labor (based on an idea from Chris Arnold):

It's a pretty great reproduction!

Colin's (and Chris's) Approach

The basic idea is to use the lowercase "l" (ell) Wingding character–which is a disc–as the caption for a command button. Colin then toggles the .Alignment property between Left and Right depending on whether the button should be on or off.

The one problem with using a plain command button is that there is no way to make it a bound control. The toggle button solves that issue. The only problem? There is no Alignment property on the toggle button control. However, we can overcome that by adding leading or trailing spaces to the caption.

(I should note here that it's not possible to add trailing spaces to the toggle button's caption via the Property Sheet. Access will trim them in an effort to be "helpful," but you can add them via code.)

We also need to make sure that we update the formatting of the toggle button to coincide with the values it represents. We do this by updating the formatting in the Form_Current and the toggle control's AfterUpdate events.

Taking It to the Next Level

When I looked at the sample code, there were a few things I thought I could tweak to make the control more practical for regular usage:

Use a toggle button instead of command button to allow direct binding to Yes/No fields
Script creation of the control so there are fewer manual steps
Use a standard text box with IIf() to display the "ON" / "OFF" label (also made possible by using a toggle button instead of command button)

Screenshots

Here are some samples of my buttons in action:

This concept works really well for single-row settings tables, as you can bind each toggle button directly to an associated field:

As a nice bonus, the labels on the left ("Report Shading", etc.) are associated with the toggle buttons, so clicking on the label will toggle the value of the button.

The concept does not work so well on a continuous form, since the change we make in code to the toggle button caption gets applied to every control:

The Code

Here's the code. Just copy and paste it into a standard code module:


' ----------------------------------------------------------------
' Procedure : CreateModernOnOff
' DateTime  : 7/26/2022 22:57
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/modern-on-off/
' Purpose   : Create a modern-looking on/off button on an Access form.
' ----------------------------------------------------------------
Sub CreateModernOnOff(FormName As String, _
                      Optional IncludeOnOffTextBox As Boolean = False)
    'Open the form in design view...
    If SysCmd(acSysCmdGetObjectState, acForm, FormName) = 0 Then
        '...if it's not already open or...
        DoCmd.OpenForm FormName, acDesign
    ElseIf Forms(FormName).CurrentView <> 0 Then
        '...if it is open but not in Design view
        DoCmd.OpenForm FormName, acDesign
    End If
    
    'Create the toggle button in the upper left corner of the form
    '   (you can move it manually in the form designer later)
    Dim ToggleBtn As ToggleButton
    Set ToggleBtn = CreateControl(FormName, acToggleButton)
    With ToggleBtn
        'Disable bevel effect
        .Bevel = 0
        
        'Ensure shape is a rounded rectangle
        .Shape = 2
        
        'Disable all tinting and shading
        .BackTint = 100
        .BorderTint = 100
        .HoverTint = 100
        .PressedShade = 100
    
        'Set border properties
        .BorderStyle = 1 'Solid
        .BorderWidth = 3
        
        'Set optimal height/width
        .Height = 300
        .Width = 780
        
        'Set Font properties
        .FontName = "Wingdings"
        .FontSize = 10
        .FontBold = True
        
        'Set properties for when the control is OFF
        .BackColor = vbWhite
        .ForeColor = vbBlack
        .HoverColor = vbWhite
        
        'Set properties for when the control is ON
        .PressedColor = vbBlue
        .PressedForeColor = vbWhite
        
        'Call the FormatToggle() function whenever the control is toggled ON/OFF
        .AfterUpdate = "=FormatToggle([ActiveControl])"
        
        'Format the control as OFF for design purposes
        .Caption = "l  "   'lowercase "l" is a filled circle glyph in the Wingdings font
        .BorderColor = vbBlack

    End With
    
    Debug.Print "Be sure to add the line..." & vbNewLine
    Debug.Print "    FormatToggle Me." & ToggleBtn.Name & vbNewLine
    Debug.Print "...to the Form_Current() event handler for form " & FormName
    
    If IncludeOnOffTextBox Then
        Dim TxtBox As TextBox
        Set TxtBox = CreateControl(FormName, acTextBox)
        With TxtBox
            .ControlSource = "=IIf(" & ToggleBtn.Name & ", 'ON', 'OFF')"
            .BorderStyle = 0 'Transparent
            .Enabled = False
            .Locked = True
            .Left = ToggleBtn.Width + 100
            .Height = ToggleBtn.Height
        End With
        
    End If
End Sub

'Companion function to CreateModernOnOff() procedure
'Source    : https://nolongerset.com/modern-on-off/
Function FormatToggle(ToggleBtn As ToggleButton)
    Const Buffer As Long = 2
    With ToggleBtn
        If .Value Then
            'When button is ON, leading spaces force
            '   disc icon to the right
            .Caption = Space(Buffer) & "l"
            .BorderColor = vbBlue
        Else
            'When button is OFF, trailing spaces force
            '   disc icon to the left
            .Caption = "l" & Space(Buffer)
            .BorderColor = vbBlack
        End If
    End With
End Function

Usage

After pasting the above code into a standard module, you create new toggle buttons with the following commands via the Immediate Window:

CreateModernOnOff "MyFormName"

CreateModernOnOff "MyFormName", True   '<-- include an ON/OFF matching text box

The code will create the toggle button (and, optionally, the ON/OFF matching text box) in the upper left of the form. You can then manually reposition the control(s) where you want them.

The last thing you will need to do is copy the generated FormatToggle Me.ToggleBtnName code from the immediate window to the form's Current event. When using the toggle buttons as bound controls for Yes/No fields, this will ensure that the formatting of the buttons is up to date whenever you change records.

Sample Database

ModernOnOff.zip

UPDATE [2022-07-27]: I uploaded a YouTube video demonstrating the implementation of this technique and also published a second version of the FormatToggle() function that works better with continuous forms.