CheckBox and ToggleButton with Excel VBA

The CheckBox control allows the user to make a selection.
A checkbox usually has two states: checked and unchecked, but it can be configured to select from three alternatives.

A CheckBox has the same main properties as an OptionButton — Value and Caption. In addition, the CheckBox has a unique property, TripleState, which enables the choice of three alternatives. The possible values of the TripleState property are:

• False – selection between two alternatives (True and False), i.e., the checkbox can only be in two states: checked or unchecked.
• True – selection between three alternatives (True, False, and Null), i.e., the checkbox can be checked, unchecked, or neutral.

The ToggleButton control provides the user with exactly the same functionality as a CheckBox, but visually it appears as a button.

The main event of both CheckBox and ToggleButton is the Change event.

CheckBox and Managing Chart Elements Display

Returning to the sales report of the company Almateus, let’s create a simple application that controls the appearance of a chart. Specifically:

• when the checkbox is checked, the chart will be displayed with a shadow;
• when the checkbox is unchecked, the chart will be displayed without a shadow.

Steps:

• On the worksheet, allocate the range B2:B7 for sales volumes and the range A2:A7 for the country names where these sales were made.
• Based on the range A2:B7, create a chart.
• Create a CheckBox and, using the Properties window, assign the property values shown in next Table.
• In the worksheet module Sheet1, type the required code.

Table. Property values set in the Properties window

When the Show chart shadow checkbox is checked, the chart will be displayed with a shadow; when it is unchecked, the shadow will be removed.

All embedded charts belong to the ChartObjects collection. Since in our case there is only one chart, it can be identified by its number, which will naturally be 1.
The display of the shadow is controlled by the Shadow property of the Chart object.

ToggleButton and Displaying Comments

We will also demonstrate the ToggleButton using the sales report of the company Almateus. In this project, comments will be included.

• When the ToggleButton is set (on), all comments will be displayed.
• When it is cleared (off), all comments and their indicators will be hidden.
• In addition, the ToggleButton caption will change from Comments displayed to Comments hidden.

Steps:

• On the worksheet, allocate the range B2:B7 for sales volumes and the range A2:A7 for country names.
• In some of the cells in the range B2:B7, insert comments using the New Comment button located in the Comments group on the Review tab of the ribbon.
• Create a ToggleButton and, using the Properties window, set its property values as shown in Table 4.13.
• In the worksheet module Sheet1, type the code (see file 10-DisplayComments.xlsm on the CD).

Table. Property values set in the Properties window

When the ToggleButton is switched on, all comments are displayed.
When switched off, all comments and their indicators are hidden, and the button caption changes to Comments hidden.

The display of comments and their indicators is controlled by the DisplayCommentIndicator property of the Application object.