Running a macro based on cell value that is updated by formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GTVT06 - 24 Jul 2007 14:43 GMT

Hello can someone help me with this?
When I use the VBA code below, the macro will run when I update the
contents in cell "G28" manually however "G28" is a formula so I want
the macro to run when "G28" is updated automatically due to its
formula and not due to manual entry. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("G28"), rng) Is Nothing Then
If Range("G28").Value = "Yes" Then Call
CDO_Mail_Small_Text
End If
End If
EndMacro:
End Sub

I got the code from the examples at this web site http://www.rondebruin.nl/mail/change.htm
and it says that this example is supposed to work if the cell contents
is a formula but I cant get it to work.

Reply to this Message

Jim Cone - 24 Jul 2007 18:46 GMT

Private Sub Worksheet_Calculate()
If Me.Range("G28").Value = "Yes" Then
MsgBox "Yes"
End If
End Sub

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"GTVT06" <gtvt06@hotmail.com>
wrote in message
Hello can someone help me with this?
When I use the VBA code below, the macro will run when I update the
contents in cell "G28" manually however "G28" is a formula so I want
the macro to run when "G28" is updated automatically due to its
formula and not due to manual entry. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("G28"), rng) Is Nothing Then
If Range("G28").Value = "Yes" Then Call
CDO_Mail_Small_Text
End If
End If
EndMacro:
End Sub

I got the code from the examples at this web site http://www.rondebruin.nl/mail/change.htm
and it says that this example is supposed to work if the cell contents
is a formula but I cant get it to work.

Reply to this Message

GTVT06 - 26 Jul 2007 14:14 GMT

Thanks Jim! I guess I needed a Worksheet_Calculate function rather
than a Worksheet_Change function. I tweeked your coding like below and
it works! thanks again!

Private Sub Worksheet_Calculate()
If Range("C19") <> Range("I14") Then
Call CDO_Mail_Small_Text
Range("C19").Value = Range("I14")
End If
End Sub

> Private Sub Worksheet_Calculate()
> If Me.Range("G28").Value = "Yes" Then
[quoted text clipped - 31 lines]
> and it says that this example is supposed to work if the cell contents
> is a formula but I cant get it to work.

Reply to this Message

Rate this thread: