Excel Training VBA 2 Lesson 18

Option Buttons and Checkboxes UserForm Controls Part 2

Information Helpful? Why Not Donate | Free Excel Help
<<Previous Lesson | NEXT LESSON>> | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX

Option Buttons and Checkboxes in Detail

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

Information Helpful? Why Not Donate | Free Excel Help
<<Previous Lesson | NEXT LESSON>> | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX