EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

 Excel Training VBA 2 Lesson 18

 

Option Buttons and Checkboxes UserForm Controls Part 2


<<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:


 

Private Sub CheckBox1_Click()

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

 


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:


 
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_Deactivate()
    Application.Calculation = AppCalc
End Sub
 
Private Sub UserForm_Initialize()
    AppCalc = Application.Calculation
End Sub

 


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
        ToggleButto
n1.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

Control

Description

Label

Displays text that cannot be edited by the user.

TextBox

Displays text that the user can edit.

ListBox

Displays a list of items from which the user can select.

ComboBox

Combines the function of a TextBox and a ListBox.

CheckBox

Displays an option that can be turned on or off.

OptionButton

Similar to a CheckBox, but only one OptionButton in a group can be "on" at one time. To create a group of OptionButton controls, place a Frame control on the UserForm first, then draw the OptionButton controls on the Frame.

ToggleButton

A button that can be either "up" or "down" permitting the user to select and deselect items.

Frame

Used to group other controls, such as OptionButtons.

CommandButton

Displays a button that's selected by the user to carry out some action.

TabStrip

Displays two or more tabs at the edge of the UserForm, permitting the user to select from multiple "pages."

MultiPage

Does essentially the same thing as a TabStrip, but is easier to use.

ScrollBar

Displays a vertical or horizontal scrollbar on the UserForm.

SpinButton

Increments and decrements numbers by clicking with the mouse.

Image

Displays an image.


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