Worksheet events problem

[daniel.hurst]

Hi,

I have built simple calculator based on sheet cells, for example B5 and
B7, and two OptionButtons 'addition' and 'subtraction'; outcome is B9
cell. Code for these operation is in Module:

---------------------------------------------------------------------------------------
Public Function MyFun1(a As Double, b As Double, c As Double)

MyFun1 = a + c * b

End Function
---------------------------------------------------------------------------------------

In Sheet1 Module is code for OptionButtons events

----------------------------------------------------------------------------------------
Dim x As Double
Dim y As Double
Dim z As Double

Private Sub OptionButton1_Click()
Call MyFun2
End Sub

Private Sub OptionButton2_Click()
Call MyFun2
End Sub

Private Sub MyFun2()

x = Range("B5").Value
y = Range("B7").Value

Select Case True

Case OptionButton1.Value
z = MyFun1(x, y, 1)
Range("B9") = z

Case OptionButton2.Value
z = MyFun1(x, y, -1)
Range("B9") = z
End Select

End Sub
----------------------------------------------------------------------------------------
When I change value in B5 or B7, in B9 is old result. I get corret
outcome just after I switch OptionButtons. What kind of Worksheet
events, Change or Calculate, to use and how, when I want to get always
proper result alter I change value in B5 or B7?

Regards

Dan

 

[Mike Fogleman]

Add this to your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$5" Or Target.Address = "$B$7" Then
MyFun2
Else
Exit Sub
End If
End Sub

Mike F

 

[daniel.hurst]

Mike,

thanks for quick answer.

Regards

Dan