change language:

On Excel and Word VBA userform are default Checkboxen, Option buttons (radio buttons) or Toggle buttons available to be able to record user-friendly simple choices (for instance enabled or disabled). The disadvantage of Checkboxes and Option buttons is that they cannot be made larger or smaller. As a result, readability sometimes leaves much to be desired. An alternative is to use a Switcher for this:

switcher control

The advantage of this Switcher is that the size can be easily adjusted and that a color also shows which choice has been made. Because the form also shows that the switcher moves from left to right or vice versa, it provides a pleasant user experience. This makes the forms more dynamic. However, the Switcher is not a standard form control that can be added.

However, a Switcher control can be made fairly easily. This can be done using a Frame control and Label control. To do this, first add a new Frame to a form and give it a dark gray background color (e.g. &H00C0C0C0&) and remove the Caption. Name this frame frSwitcher1. Set the width of this frame to 66 and the height to 33.

Next, in the Frame, place a Label and give that Label a white background color. Make sure the caption of the label contains no text. Name this label lblSwitcher1. For Left and Top set the value 4 and set the width and height to 22.

In the same way, if desired, several switchers can be added, whereby the last number of the control name must be increased each time (frSwitcher2 en lblSwitcher2, etc.).

Then add the following code to the form. This code assumes the presence of 2 switchers on the form.

'--------------------------------------------------------------------------------------------------------------------------
' Author    : Manfred van den Noort
' Copyright : © 2022-2024 worksheetsvba.com, all rights reserved
' Date      : 2022-12-16
' Version   : 1.0
' Purpose   : Switcher Control
'------------------------------------------------------------------------------------------------------------------------------
Private Sub frSwitcher1_Click()
    MoveSwitcher 1
End Sub

Private Sub lblSwitcher1_Click()
    MoveSwitcher 1
End Sub

Private Sub frSwitcher2_Click()
    MoveSwitcher 2
End Sub

Private Sub lblSwitcher2_Click()
    MoveSwitcher 2
End Sub

Private Sub MoveSwitcher(iSeqNo As Integer)
    Dim i As Integer, sngTimer As Single, iLeftStep As Integer, frSwitcher As MSForms.Frame, lblSwitcher As MSForms.Label
    Set frSwitcher = Controls("frSwitcher" & iSeqNo)
    Set lblSwitcher = Controls("lblSwitcher" & iSeqNo)
    If frSwitcher.Tag = False Then
        iLeftStep = 1
    Else
        iLeftStep = -1
    End If
    For i = 1 To 32
        sngTimer = Timer
        Do While Timer - sngTimer < 0.005
        Loop
        lblSwitcher.Left = lblSwitcher.Left + iLeftStep
        Repaint
    Next
    With frSwitcher
        .Tag = Not CBool(.Tag)
        If .Tag = False Then
            .BackColor = &HC0C0C0
        Else
            .BackColor = &HF0B000
        End If
    End With
End Sub

Below you can download an example file in which this example of a Switcher can be seen. An advanced version of the Switcher has also been added to the sample file. With this version you can add Switchers where you can determine the size yourself. This size is determined by the size of the Frame placed on a form. This group box is then made into a Switcher object using a class, after which this class further configures the switcher and handles the events. For now, this object only contains a Value property and a Change event, but then it can be easily expanded if desired.

Click on the button below to download an example file.
zip-16Switcher control 1.0
 

Questions / suggestions

Hopefully, this article helped you adding a switcher to a VBA userform. If you have any questions about this topic or suggestions for improvement, please post a comment below.

arrow_up