Option Button Controls

Each option button has code similar to the following:

Private Sub OptionButton1_Click()

Call Options End Sub

The processing for all the buttons is carried out in the following procedure, which is in the class module behind the Profit worksheet that holds the previous event procedures:

Private Sub Options()

Dim dCostFactor As Double

Dim IScrollBarMax As Long

Dim lScrollBarMin As Long

'Determine which option button is True

Select Case True


Case OptionButtonl.Value dCostFactor = 0.63 lScrollBarMin = 50000 lScrollBarMax = 150000


Case OptionButton2.Value dCostFactor = 0.74 lScrollBarMin = 25000 lScrollBarMax = 75000


Case OptionButton3.Value dCostFactor = 0.57 lScrollBarMin = 10000 lScrollBarMax = 30000


Case OptionButton4.Value dCostFactor = 0.65 lScrollBarMin = 15000 lScrollBarMax = 30000

End Select

'Apply factors

Range("B15").Value = dCostFactor ScrollBarl.Min = lScrollBarMin ScrollBar1.Max = lScrollBarMax ScrollBar1.Value = lScrollBarMax

End Sub

The Select Case structure is used here in an unusual way. Normally you use a variable reference in the first line of a Select Case and use comparison values in the Case statements. Here, you used the value True in the Select Case and referenced the option button Value property in the Case statements. This provides a nice structure for processing a set of option buttons where you know that only one can have a True value.

Only one option button can be selected and have a value of True in the preceding worksheet, because they all belong to the same group. As you add option buttons to a worksheet, the GroupName property of the button is set to the name of the worksheet— Profit, in this case. If you want two sets of unrelated option buttons, you need to assign a different GroupName to the second set.

Options uses the Select Case structure to carry out any processing that is different for each option button. The code following the End Select carries out any processing that is common to all the option buttons. This approach also works very well when the coding is more complex, and also when the code is triggered by another control, such as a command button, rather than the option button events.

0 0

Post a comment