Macro to run when cell changes

[newguy]

I am trying to get this code to work that when the cell value changes
it calls another macro that sends and email but I can't get it to send
the email. The other macro on it own works.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$AE$1573" Then
Mail_small_Text_Outlook
End If
End Sub

 

[Jon Peltier]

1. Is the Worksheet_Change procedure in the module corresponding to the
sheet with the changing cell?

2. This is a better way to test the target:

If Not Intersect(Target, Range("$AE$1573")) Is Nothing Then

- Jon

 

[Mike]

module1.Mail_small_Text_Outlook

 

[newguy]

Yes the procedure is in the module with the changing cell.

 

[newguy]

That didn't work either

 

[Gord Dibben]

You haven't changed anything so your event won't fire.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$AE$1573" <> "" Then
Mail_small_Text_Outlook
End If
End Sub


Gord Dibben MS Excel MVP

 

[newguy]

So how do I get it to recognize the change. The cell I am referencing
is a sum and when the sum changes I want to send and email. The code
above gives my a "type mismatch" error.

Thanks

 

[Jon Peltier]

If a formula recalculates, it does not count as a "change", because the
formula itself is unchanged. You need to look at changes in the precedents
of the formula. You could simply fire your code off the Worksheet_Calculate
event, but it's more involved to limit it to changes in the calculated value
of a given cell. You have to save the old value of the cell, compare it to
the current value, and do your stuff if the values differ.

- Jon