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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"newguy" wrote in message
...
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

module1.Mail_small_Text_Outlook

"newguy" wrote:

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

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


On Oct 9, 12:44*pm, "Jon Peltier"
wrote:
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"newguy" wrote in message

...

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

That didn't work either

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

On Thu, 9 Oct 2008 09:32:49 -0700 (PDT), newguy wrote:

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

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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"newguy" wrote in message
...
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