The OptionButton control allows you to select one of several mutually exclusive alternatives. Option buttons are usually displayed in groups corresponding to the available choices. Grouping can be performed using either the Frame control or the GroupName property of the OptionButton object.
The main events of an option button are Click and Change.
The main property of an option button is Value, which returns or sets its state. If this property is set to True, the option button is selected; if False, it is cleared.
Option Buttons and Sales Volumes
To demonstrate an example of using option buttons, let’s return to the sales report of the company Almateus. Suppose we need to compile a list of countries where sales volume is either the maximum or the minimum.
To solve this problem, perform the following steps:
- On the worksheet, allocate the range B2:B7 for sales volumes and the range A2:A7 for the names of the countries where these sales were made.
- In cell B8, enter the formula for calculating total sales: =SUM(B2:B7).
- Create two option buttons and, using the Properties window, set their property values as shown in next Table.
- In the worksheet module Sheet1, type the required code.
Table. Property values set in the Properties window
| Object | Property | Value |
| OptionButton | Name | optMax |
| Caption | Maximum Sales Volume | |
| OptionButton | Name | optMin |
| Caption | Minimum Sales Volume |
Selecting the Maximum Sales Volume or Minimum Sales Volume option button will determine the required sales volume and generate a list of the corresponding countries.
- The calculated sales volume is displayed in cell G1.
- The explanatory label is displayed in cell G2.
- Starting from cell G3, the compiled list of countries is output.
The countries with the corresponding sales volumes are found using the Find method of the Range object. All cells found with the Find method are combined into a single range. Based on this range, a new range containing the country names is created using the Offset method of the Range object, which is then copied to the specified location using the Copy method.
Important: The program automatically determines the range where the search should be performed. The only essential detail is that the code accounts for the fact that the first and last rows of column B contain not data but the table field header and the total sum. Therefore, these rows should not be considered. This is achieved by sequential application of the Resize and Offset methods.
Finally, the AutoFit method automatically adjusts the width of column G so that all displayed information fits properly.