Calculate Event - Run macro only once

[nt82]

hello,

I have some issue with below code...what it does is when b1>A1 then it continously runs macro BUYBEAN..what I want is to run only one time..till there is change in value in A1 and B1

Private Sub Worksheet_Calculate()
On Error GoTo ws_exit
    Application.EnableEvents = False
If Range("b1") > Range("A1") Then
 Call BUYBEAN
Else
Call SELLBEAN
End If
ws_exit:
    Application.EnableEvents = True
End Sub

 

[MrKowz]

Are A1 and B1 calculated cells (i.e. they have a formula)? If so, the easiest way would be to eliminate this macro and create a worksheet_change macro that monitors changes for the manual-entry cells that feed into A1 and B1.

 

[nt82]

Actually they are values updated dynamically through a DDE link ..from an external application...

I would have used worksheet_change but it doesnt recognize any value if its DDE related...

 

[JamesW]

Hi,

Try putting it inside Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:B1")) Is Nothing Then
        On Error GoTo ws_exit
        Application.EnableEvents = False
        If Range("b1") > Range("A1") Then
            Call BUYBEAN
        Else
            Call SELLBEAN
        End If
ws_exit:
        Application.EnableEvents = True
    End If
End Sub

 

[nt82]

somehow change event is not recognizing my value in A1 and B1 so this doesnt shoot any macro..

 

[nt82]

worksheet_change doesnt trigger a macro