Mutually exclusive Checkbox in Excel (Toggle)

Question

I have spreadsheet with 50+ checkboxes from the forms toolbar. In a few instances you can only check 1 checkbox from a group i.e you check one checkbox from checkbox 1 to checkbox 5.

I'm trying to achieve this without any code if possible. Option button is not preferred since I want an uniformed spreadsheet.

How can you group the checkbox1 to checkbox5 so that they become mutually exclusive for a group.

Thank you

Answer 1

You might want to use a radio button, with possibly a separator between the groups, as this also signifies to the end-user that those fields are mutually exclusive.

A radio button, by definition, is Mutually Exclusive within a group.

Remember that using checkboxes in places where you should be using radio boxes will be confusing your end-users, as they wonder why that checkbox is suddenly de-activated, while when using radio buttons the confusion factor is non-existant.

Answer 2

If you can't use Option Buttons, try this code

Private Sub CheckBox1_Change()

    With Me.CheckBox1
        If .Value Then ClearGroup .GroupName, .Name
    End With

End Sub

Private Sub CheckBox2_Change()

    With Me.CheckBox2
        If .Value Then ClearGroup .GroupName, .Name
    End With

End Sub

Private Sub CheckBox3_Change()

    With Me.CheckBox3
        If .Value Then ClearGroup .GroupName, .Name
    End With

End Sub

Private Sub ClearGroup(sGroup As String, sName As String)

    Dim ole As OLEObject

    For Each ole In Me.OLEObjects
        If TypeName(ole.Object) = "CheckBox" Then
            If ole.Object.GroupName = sGroup And ole.Name <> sName Then
                ole.Object.Value = False
            End If
        End If
    Next ole

End Sub