Land Of Oorang

Every now and again I will run across a post like this:

I have created (a) form with check boxes. I am trying program the form so that when a particular box is checked the user cannot check another box in that row. For example, the user should not be able to check "yes", "no" and/or "don't know" at the same time. This is the code I an using, but I keep recieving error messages. What is the problem?

Often such posts are followed by solutions that involve catching the click event of every single CheckBox control, then manually checking each CheckBox that shouldn't be selected and flipping the value: 

Private Sub CheckBox1_Click()
    If Me.CheckBox1.Value Then
        If Me.CheckBox2.Value Then Me.CheckBox2.Value = False
        If Me.CheckBox3.Value Then Me.CheckBox3.Value = False
    End If
End Sub

Private Sub CheckBox2_Click()
    If Me.CheckBox2.Value Then
        If Me.CheckBox1.Value Then Me.CheckBox1.Value = False
        If Me.CheckBox3.Value Then Me.CheckBox3.Value = False
    End If
End Sub

Private Sub CheckBox3_Click()
    If Me.CheckBox3.Value Then
        If Me.CheckBox1.Value Then Me.CheckBox1.Value = False
        If Me.CheckBox2.Value Then Me.CheckBox2.Value = False
    End If
End Sub

    If this code made you cringe, you are not alone. As you can imagine, when you (or the poor soul following you) has to add/rename/delete a CheckBox in future this is going to be real maintenance nightmare. Especially if you, like the user above have a lot of controls. Fortunately there is an easy solution to this problem.

    The easiest solution is, of course, to just use Option Groups. No code needed. But some people just really hate radio buttons and want the look of a CheckBox. And since some of those people might be employing you, I will direct your attention to the GroupName property of the CheckBox control. At first glance you might think all your problems are solved. It looks just like the OptionButton.GroupName property! Yay! Sadly, for whatever reason, in VBA this property will hold a value, but the mutual exclusion of an Option Button Group is just not implemented automatically. But don't despair! It's still pretty easy to work around this. With a Class Based approach you can reduce the code in your Form to this:

Private m_cogGroup1 As New CheckOptionGroup
Private m_cogGroup2 As New CheckOptionGroup

Private Sub UserForm_Initialize()
    m_cogGroup1.InitializeGroup "Group1", Me.Controls
    m_cogGroup2.InitializeGroup "Group2", Me.Controls
End Sub

To do this:

1. Import the two classes posted below.
2. Go ahead and use the GroupName property to specify your groups of checkboxes (just like you would an option group).
3. Implement as shown above, using one array of CheckOptionGroup for each group.

To make life a little easier, I have created an Example Workbook.

If you just want to copy/paste the code use these:

CheckOption.cls

CheckOptionGroup.cls

These are also stored in the Code Archive.

If you hang out on MrExcel then you know why this is funny (click for a larger view):

You can visit Nate Oliver's blog here: http://msmvps.com/blogs/nateoliver/