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!
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)
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:
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:
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
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.
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.