OptionButton - how to determine state

[Bill D.]

This may be better done with CheckBoxes but .....

I have twoGroups of two OptionButtons. Each OptionButton represents a
price of a component to make up a complete assembly. The total assembly
price can use only one item from each group in any given assembly.
Example:

Item 1 = $100 (OptionButton1, Group 1)
Item 2 = $200 (OptionButton2, Group1)

Item 3 = $300 (OptionButton1, Group2)
Item 4 = $400 (OptionButton2, Group2)

Total = sum of any ONE item from EACH of the above groups

The Option buttons work correctly, only one button in a group can be
selected at a time. However clicking an already selected button
continues to add the item price to the total and clicking the unselected
button adds its price to the total without subtracting the price of the
now deselected button.
How can I:

1) Prevent a selected button from adding its price to the total if
clicked again
2) Subtract the previously selected button's price from the total when
selecting the unselected button in the group

Following is the code (this is for a test worksheet, the actual sheet
has many more buttons)

Sub test_buttons()
Dim vColumn As Integer
Dim vSubTotalRow As Integer
buttonclicked = Application.Caller
vColumn = 4

'*********** Set vSubTotalRow based on button clicked

ButtonType = Left(buttonclicked, 5)
If ButtonType = "optio" Then
numb = Replace(buttonclicked, "optionbutton", "")
Select Case numb
Case numb = 1 To 4
vSubTotalRow = 14
End Select
End If

'************************ Set vRow for button clicked

Select Case buttonclicked
Case "optionbutton1" '<===Group 1, Button 1
vRow = 7
GoSub MyRoutineOptionButton
Case "optionbutton2" '<=== Group 1, Button 2
vRow = 8
GoSub MyRoutineOptionButton
Case "optionbutton3" '<=== Group 2, Button 1
vRow = 10
GoSub MyRoutineOptionButton
Case "optionbutton4" '<=== Group 2, Button 2
vRow = 11
GoSub MyRoutineOptionButton
End Select

Exit Sub

MyRoutineOptionButton:
If ActiveSheet.OptionButtons(buttonclicked).Value = xlOn Then
Cells(vSubTotalRow, vColumn) = _
Cells(vSubTotalRow, vColumn) + Cells(vRow, vColumn)

Return
End Sub

 

[Tom Ogilvy]

It would probably be easier to recalculate the price each time by running
through the option buttons and seeing which is selected.