Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.

Option Buttons and Checkboxes in Detail

The Difference Between the Two

OptionButtons and CheckBoxes are perhaps one of the most frequently used Controls when designing a UserForm.  Unfortunately, many users seem to get the two confused with each other and end up using a CheckBox when it should be an OptionButton and vice verse.  The rules of which one to use and when are really quite simple and boils down to basically if the user should be allowed to make one selection only from a choice of many, then it should be OptionButtons.  If the user should be allowed to make one or more choices from many, then it should be CheckBoxes.  To further this more, an OptionButton cannot be used on its own, it must be part of a group of two or more OptionButtons.  A CheckBox on the other hand can and very often is used on its own which enables the user to select or deselect it.  If you tried to do this with an OptionButton, the user would only be able to select the OptionButton and without any other OptionButtons forming part of the same group, they would not be able to deselect it.

Let's look now at each of these in detail and also use some examples that we can follow.

OptionButtons

As mentioned above, it is not feasible to use an OptionButton on its own, so when adding one to a UserForm, it means that it will become one of a group of two or more.  When an OptionButton is added, one Property which becomes very important is the GroupName of the OptionButton.  The correct setting for this would be a one-word string.  Each OptionButton on a UserForm which has the same GroupName results in the creation of a group of mutually exclusive OptionButton Controls.   The end result of this will be that when one of the group is selected, the remainder of the group will be de-selected.  There is no exception to this rule. 

Another aspect of this which can often confuse designers is the fact that each OptionButton with the same GroupName is only mutually exclusive to the Container which is housing them.  A Container is simply an Object which contains other Objects.  The Container is normally a UserForm Control itself.  However, it could be a MultiPage Control.  For example, we could place a MultiPage Control onto a UserForm containing three separate pages.  On each of these pages we could place a group of two or more OptionButtons.  We could then give the exact same GroupName to each and every OptionButton.  This would then mean that the OptionButtons would not so much be grouped by their GroupName, but rather by the individual pages on the MultiPage housing the OptionButtons.  This is because each page of a MultiPage is a Container. 

Another Container that can be used to group OptionButtons and often is, is the Frame Control.  When a Frame Control is added to a UserForm, we can then place OptionButtons within the Frame Control and then these OptionButtons will automatically become part of the same group.  Again, it is important to note that the Frame Control is a Container which can contain other Objects.  When using the Frame Control, in conjunction with OptionButtons, it is not necessary to supply a GroupName to the OptionButton, by default the GroupName is always empty text.  While this can be useful for a UserForm which may contain several groups of OptionButtons, there are some advantages to using the GroupName Property over the use of a Frame Control.

1. You reduce the number of Controls on the UserForm and in doing so can enhance performance and reduce the size of the UserForm.

2. You have far more design flexibility.  When you use a Frame Control to create a group, all the OptionButtons must be within the Frame Control's boundary.

3. You can create buttons with transparent backgrounds which can enhance the visual appearance of your form.  It is important to note that the Frame Control is not a transparent Control.

Regardless of which method we choose, an OptionButton simply returns a Boolean value of True or False.  The default value for OptionButtons is False.  The default Property for an OptionButton is its Value Property.

Lets now use a very simple example to demonstrate this, so that you can see it visually.

Insert a new UserForm into a Workbook and to it add two OptionButtons and ensure their names are OptionButton1 and OptionButton2.  Now to the Private Module of the UserForm Object, add the code as shown below.

You will see that by clicking on the OptionButton you are always returned True in the MessageBox for both buttons.  The important thing to note here is that even by clicking either OptionButton when their value is True, the OptionButton remains checked and the Click Event is not fired.  What this simply means is that the Click Event will only fire for an OptionButton when it is selected  while having a False value.  Note also that we have not specified a Property to be returned by the OptionButton but it defaults to the Value Property anyway.

OptionButton with a Control Source

If we use an OptionButton and then also assign a Control Source ie; a cell to the Control, it can behave slightly differently. 

For example, using the same two OptionButtons as in the above example, remove the Click Event code for both OptionButtons from the UserForm Private Module.  Ensure cells A1 and A2 are empty on the active worksheet and assign OptionButton1's Control Source to A1, and OptionButton2's Control Source to A2.  Set the Value Property of OptionButton1 to True.  Now run the UserForm and move it out the way so that you can easily see cells A1 and A2 on the active worksheet.  Even though we have set the Value Property of OptionButton1 to True, its Control Source will still be empty while the Control Source for OptionButton2 will be reading False.  You will notice now that as you click each button, the Value of the button will be reflected in the cell that it is bound to.  You will also notice that selecting an OptionButton that currently is False, or unchecked will first deselect the other OptionButton (leaving both OptionButtons unchecked) and it is only when you click the OptionButton for a second time that it returns a Value of True. 

CheckBoxes

Lets now look at the CheckBox Control and see some uses for it.  One very important thing that we need to understand before we look at the CheckBox Control is that is also has a GroupName Property, however,  using the GroupName Property, has no affect whatsoever on the CheckBox controls affecting eachother ie; setting a group of three CheckBoxes to the same GroupName and selecting one would not deselect any others that were currently selected.  Basically, the GroupName Property is of no use for the CheckBox.  Why it is included, I don't know!!!! 

As stated initially, we would use a CheckBox or CheckBoxes when the user is able to select or deselect one or more choices.  Again, as with the OptionButton Control, its default Property is the Value Property and its default Event is the Click Event.

Lets now use a very similar example as to the example we used for the OptionButton Controls to show how the CheckBox Control operates.

On a UserForm, place two CheckBoxes and ensure their names are CheckBox1 and CheckBox2 and then in the Private Module of the UserForm Object insert the code as shown below.

Run the UserForm and select/deselect each CheckBox.  You will notice that selecting one CheckBox has no effect on the other and we are also able to deselect a CheckBox once it is checked simply by clicking it again. 

Although, as with the OptionButton, a CheckBox simply returns True or False, the uses of a CheckBox can vary far and wide.  Lets assume that we have a UserForm which, when a button on it is clicked, performs a very lengthy procedure which could sped up greatly by enabling the user to switch calculation to manual by selecting a single CheckBox.

On a UserForm, place one CheckBox called CheckBox1 and change its Caption Property to the text Turn Off Calculations.  Now in the Private Module of the UserForm, place the code as shown below:

Now run this UserForm and check the CheckBox so that it becomes checked.  Now simply close the UserForm, go to Tools>Options>Calculation and you should see that the calculations for Excel have been switched to manual.  A very simple use of a CheckBox, but can greatly speed up the performance of a Procedure.  It does, however, have the possibility to cause one potential disaster.  This is that the user may neglect to switch the calculation of the workbook back to automatic and carry on using the workbook not realising it is in manual calculation mode.  For this reason, when doing something with any setting on a User's PC, it is always very important that we first pass the state of the setting that we will be changing to a Variable so that we can easily set it back to how it was automatically on conclusion.  In the example we have used above, it could be done in the following way:

The all important piece of code here that we need to take note of is that in the initialisation of the UserForm, we have passed to a Variable dimensioned as XlCalculation, the current calculation state of the workbook.  The Variable has been declared at the Module level so that we can use it again when the UserForm is deactivated.  If you use a method similar to this, it is important that you place the code in the correct Events, and that we have no code that could possibly destroy our Variable storing the calculation state.  About the only code which could cause this problem would be the use of the End Statement.  If you feel that this may be possible, rather than run the risk, you might be better to store the setting in a cell on a hidden worksheet.

TripleState Property

There is one other Property which can be applied to a CheckBoxes but not an OptionButton and this is the TripleState Property.  Although the TripleState Property exists for OptionButtons, changing it has no effect on the OptionButton.  Again, I have no idea why this exists!!!  To set the TripleState Property of an CheckBox we change it in the Properties window from its default Value of False to True.  This then gives us a third state of the CheckBox which has a Value of Null.  Null is a Value indicating that a Variable contains no valid data.  Null is the result of the explicit assignment of Null to a Variable of any operation between expressions that contain Null.  Simply put, this means that instead of the CheckBox only having a Value of True or False, it can now have the Value of Null also.   When a CheckBox is Null, it will appear as a shaded button.  One very important point we need to be aware of is that the Null state of a CheckBox does not fire the Click Event of the CheckBox. 

Perhaps the best way for the use of the TripleState Property to be demonstrated is to use the Microsoft Excel Help example. 

On a UserForm place one CheckBox named CheckBox1, a ToggleButton named ToggleButton1 and another ToggleButton named ToggleButton2

Private Sub UserForm_Initialize()
    CheckBox1.Caption = "Value is True"
    CheckBox1.Value = True
    CheckBox1.TripleState = False
   
    ToggleButton1.Caption = "Value is True"
    ToggleButton1.Value = True
    ToggleButton1.TripleState = False

    ToggleButton2.Value = False
    ToggleButton2.Caption = "Triple State Off"
End Sub
 

Private Sub ToggleButton2_Click()
    If ToggleButton2.Value = True Then
        ToggleButton2.Caption = "Triple State On"
        CheckBox1.TripleState = True
        ToggleButton1.TripleState = True
    Else
        ToggleButton2.Caption = "Triple State Off"
        CheckBox1.TripleState = False
        ToggleButton1.TripleState = False
    End If
End Sub

 

Private Sub CheckBox1_Change()
    If IsNull(CheckBox1.Value) Then
        CheckBox1.Caption = "Value is Null"
    ElseIf CheckBox1.Value = False Then
        CheckBox1.Caption = "Value is False"
    ElseIf CheckBox1.Value = True Then
        CheckBox1.Caption = "Value is True"
    End If
End Sub
 

Private Sub ToggleButton1_Change()
    If IsNull(ToggleButton1.Value) Then
        ToggleButton1.Caption = "Value is Null"
    ElseIf ToggleButton1.Value = False Then
        ToggleButton1.Caption = "Value is False"
    ElseIf ToggleButton1.Value = True Then
        ToggleButton1.Caption = "Value is True"
    End If
End Sub

Once we understand how CheckBoxes and OptionButtons operate, their uses can become quite broad and while they are normally very simple to code, there are a few pitfalls that can catch us out if we are not aware of them. 

The following table shows a general guide only as to what Controls are used for what purpose