Need a little help with Change Events

[eric3]

I have very basic self-taught working knowledge of VBA and I am not able to find the right code to accomplish a task in Excel 2003.

Working from the basic code below of calling the Sub SendMail if cell D28= DOWN, I now want to call the Sub DeleteOrders when Cell D28 CHANGES from DOWN to UP. Not when it equals DOWN or UP but only after the calculation when it changes from DOWN to UP.

Your help is very appreciated.

Private Sub Worksheet_Calculate()
If Me.Range("D28").Text = "DOWN" Then
If Not bAlert Then
'Sendmail
Call SendS4Email
bAlert = True
End If
Else
bAlert = False
End If

End Sub

[GTO]

Hi -

I am new to this and may not be of help, but if I understand your question, I believe you're looking to do something like the following.

In short, your example showed using the calculate event (which won't work, as typing or entering a word does not fire calculation), you mention using the change event, and I'm not sure as to what was apparently the first operation or option, to wit: "calling the Sub SendMail if cell D28= DOWN".

Anyways, using the change event, you could add seeing if the specific cell (range) had changed by using 'Target'. (Note: I included a fake sub to see if it was called.)



[vba]Option Explicit
Dim bAlert As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
If Me.Range("D28").Text = "UP" _
And Target.Address = Me.Range("D28").Address Then

If Not bAlert Then
'Sendmail
Call SendS4Email
bAlert = True
End If
Else
bAlert = False
End If
End Sub
Private Sub SendS4Email()
MsgBox "Hey, I was called...", 0, ""
End Sub
[/vba]

[mikerickson]

If the Calculate event is sending mail when you want it to, I assume that D28 either has a formula in it or is a precident cell for some other cell's formula. The Change event might be a better place.

I also wonder about bAlert, is it a Public boolean variable?

This will call DeleteOrders when D28 changes from DOWN to UP
[VBA]Private Sub Worksheet_Calculate()
Static LastD28 As Variant
If Range("D28").Text = "DOWN" Then
bAlert = Not (bAlert)
If bAlert Then
'Sendmail
Call SendS4Email
End If
End If

If LastD28 = "DOWN" And Range("D28").Text = "UP" Then
Call DeleteOrders
End If

LastD28 = Range("D28").Text
End Sub[/VBA]

[eric3]

That looks like what I am after. D28 is a formula so the "change" should work.

Thanks alot.