Excel VBA: OptionButtons and Checkboxes. Excel VBA 2 Lesson 6

HOME | Back to index  <Previous | Next> Buy the complete Excel course, Including VBA & Workbook examples for only $9.95 | Ask a question?

Download the UserForms Lesson 3 2007.xlsx  file for examples of using the various controls.  The Lesson 3 sheet has the examples for this lesson.

For reference (Controls in bold are covered in this lesson):

 OptionButtons and Checkboxes

OptionButtons and CheckBoxes allow the user to select items from a list.  The difference is that more than one CheckBox can be checked, while only one OptionButton can be selected.  Once an OptionButton is checked, checking another one automatically turns off the one previously checked.  If there is only one item in the list, a CheckBox has some use, but an OptionButton probably has none.  Since an OptionButton is turned off by clicking another one, a single OptionButton, once clicked, will remain selected for the duration of the UserForm.  If a once-only function is needed, then a single OptionButton would fill the need.

CheckBoxes

A CheckBox simply returns True or False.  Besides selecting from a list, it can also select between two conditions, as with a ToggleButton.  For example, if a UserForm button initiates a time-consuming procedure which would run faster if calculation is set to manual, the calculation mode can be controlled by CheckBox.

EXAMPLE

From the Lesson 3 sheet of the workbook attached, click the “CheckBox and OptionButton Demos” button for examples.

Run the UserForm and select/deselect each CheckBox.  Checking one CheckBox has no effect on the others, so more than one CheckBox can be checked.  Checked CheckBoxes can also be unchecked.  The “See” CheckBox cycles through a gray state, which will be explained later in the TripleState section.  The CheckBoxes are linked to cells D12:D14.  Their values can be seen there at all times.  D12 is blank when the “See” CheckBox is gray.

EXERCISE

Place a CheckBox1 on a UserForm and change its Caption to “Turn Off Calculations.  Insert this code in the Private Module of the UserForm:

Private Sub CheckBox1_Click()

    If CheckBox1.Value = True Then
        Application.Calculation = xlCalculationManual
    Else
        Application.Calculation = xlCalculationAutomatic
    End If

End Sub

Run the UserForm and check the CheckBox.  Close the UserForm, go to Tools>Options>Calculation and note that the calculation mode for Excel has been switched to manual.  Don’t forget to click the CheckBox again, to change it back to automatic, or a very long time can be spent looking for why a spreadsheet has stopped working.

For this reason, before changing a setting on a user's PC, it is always very important to first save the state of the setting, so it can be restored when done.  In this example, the state is saved to a variable declared at the Module level.  The value of such a variable persists after the procedure ends, so it can be used later in another procedure.  In this exercise, the state is saved when the UserForm initializes, and is restored when the UserForm is deactivated:

Dim AppCalc As XlCalculation

Private Sub CheckBox1_Click()

    If CheckBox1.Value = True Then
     Application.Calculation = xlCalculationManual
    Else
     Application.Calculation = xlCalculationAutomatic
    End If

End Sub

Private Sub UserForm_Initialize()
    AppCalc = Application.Calculation
End Sub

Private Sub UserForm_Deactivate()
    Application.Calculation = AppCalc
End Sub

It is important to place the code in the correct event handler, and that no code changes the variable.  Still, there are a number of operations that can interrupt before the cleanup code is run, it would be even better to store the setting in a cell on a hidden worksheet.  Interrupting events could include user intervention or an unexpected error.

OptionButtons

One OptionButton by itself is useless.  It is important to associate members of a group of two or more that will be mutually exclusive.  The two ways of grouping OptionButtons are the GroupName property of the OptionButton, and the Frame control. 

The Frame control is handy because its border visually sets apart the OptionButtons and its title identifies the condition being selected.  To use it, place a Frame control on the UserForm and expand it to a size somewhat larger than might be needed to hold that set of OptionButtons.  Place the OptionButtons within the frame control.  If the UserForm will have multiple sets of OptionButtons for different purposes, the Frame control is one way to differentiate sets.

The second way to group OptionButtons is with GroupName.  If the OptionButtons will be arranged in an irregular pattern, or will be added to the UserForm dynamically through code, then Frames will not work and the GroupName variable must be used.  It is normally a one word string, though if a UserForm has only one group of OptionButtons, it may be left blank.

A GroupName must only be unique to a container.  Besides UserForm and Frame, there are other containers such as the pages of a MultiPage control.  For example, a MultiPage control with three pages could have OptionButtons with the same GroupName on each page, and still function as expected. 

Each OptionButton on a UserForm which has the same GroupName, or is within a Frame, results in the creation of a group of mutually exclusive OptionButton controls.  To be mutually exclusive means that when one of the group is selected, selecting a different optionbutton of the group will de-select the first one.  There is no exception to this rule. 

Some advantages of using the GroupName property instead of a Frame control:

·         Fewer controls on the UserForm slightly enhances performance and reduces the size of the UserForm.

·         More layout flexibility, in that use of a Frame requires all the OptionButtons to be within the Frame's boundary. 

·         OptionButtons with transparent backgrounds can enhance the visual appearance of the userform.  The Frame control cannot be transparent. 

Regardless of which grouping method we choose, an OptionButton simply returns a Boolean value of True or False.

EXERCISE

Insert a new UserForm into a Workbook.  Add two OptionButtons named OptionButton1 and OptionButton2.  In the Private Module of the UserForm object, add this code:

Private Sub OptionButton1_Click()

    Debug.Print OptionButton1, OptionButton2

End Sub

Private Sub OptionButton2_Click()

    Debug.Print OptionButton1, OptionButton2

End Sub

Click OptionButton1, then OptionButton2, then OptionButton2 a second time.  In the Immediate Window, see that True is returned for the OptionButton that is clicked, and False is returned for the other, except the second time OptionButton2 is clicked.  The Click Event only runs for an OptionButton having a False value.  Note also that no OptionButton property was specified, so the code used the default Value property.

OptionButton with a Control Source

OptionButton can behave slightly differently if ControlSource is set to a cell address.

EXERCISE

            Using the same 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

·         Assign OptionButton1's Control Source to A1, and OptionButton2's Control Source to A2.  This is also called binding.

·         Set the Value Property of OptionButton1 to True.

·         Run the UserForm and move it out the way so that cells A1 and A2 can be seen on the active worksheet.  Even though the Value property of OptionButton1 is set to True, its Control Source will still be empty while the Control Source for OptionButton2 will show False.

·         Click each button and see the button Value reflect in the bound cell.  Note 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 the OptionButton is clicked for a second time that it returns True. 

EXAMPLE

From the Lesson 3 sheet of the attached file  UserForms Lesson 3 2007.xlsx, click the “CheckBox and OptionButton Demos” button.

Run the UserForm and click each OptionButton.  The OptionButtons within Frame1 are bound to cells D16:D18, and their values can be seen there.  The “See” OptionButton with Frame1 is initialized to a gray state, which will be explained next in the TripleState section.  D16 is blank when initialized.

TripleState Property

CheckBoxes and OptionButtons both have the TripleState property, which allows the Null value in addition to False and True.  Null is a value indicating that a variable contains no valid data.  CheckBoxes and OptionButtons are shaded gray when Null.

TripleState defaults to False and can be changed in the Properties window.  A TripleState CheckBox will cycle from False to Null to True as it is clicked multiple times.  An OptionButton can only be Null through code or at initialization.  Once another OptionButton is clicked, the Null is gone until the next time it is set by code.  As such, it is not very useful, so TripleState is normally used only for CheckBoxes.

The Null state of a CheckBox or OptionButton does not fire the Click Event.

EXAMPLE

From the Lesson 3 sheet of the attached file  UserForms Lesson 3 2007.xlsx, click the “CheckBox and OptionButton Demos” button for examples.

As mentioned above, the “See” CheckBox cycles through the gray state.  To see that the Null state does not fire the Click event, click the “See” CheckBox several times while watching the immediate window.  Only the values False and True run the click event handler.

The “See” OptionButton within Frame1 is initialized to the Null state.  Click it, and it changes to True.  Close the UserForm and restart it.  This time click either “Dick” or “Run.”  “See” goes from Null to False.

HOME | Back to index  <Previous | Next> Buy the complete Excel course, Including VBA & Workbook examples for only $9.95 | Ask a question?

OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.