Macro to run when cell changes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

newguy - 09 Oct 2008 17:32 GMT

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

Reply to this Message

Jon Peltier - 09 Oct 2008 17:44 GMT

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
_______

>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
[quoted text clipped - 5 lines]
> End If
> End Sub

Reply to this Message

newguy - 09 Oct 2008 17:54 GMT

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

On Oct 9, 12:44 pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:

> 1. Is the Worksheet_Change procedure in the module corresponding to the
> sheet with the changing cell?
[quoted text clipped - 19 lines]
> > End If
> > End Sub

Reply to this Message

newguy - 09 Oct 2008 18:14 GMT

That didn't work either

Reply to this Message

Mike - 09 Oct 2008 17:48 GMT

module1.Mail_small_Text_Outlook

> 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
[quoted text clipped - 5 lines]
> End If
> End Sub

Reply to this Message

Gord Dibben - 09 Oct 2008 22:19 GMT

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

>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
[quoted text clipped - 5 lines]
> End If
>End Sub

Reply to this Message

newguy - 10 Oct 2008 13:07 GMT

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

Reply to this Message

Jon Peltier - 10 Oct 2008 13:48 GMT

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
_______

> 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

Reply to this Message