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

[GTVT06]

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.

 

[Jim Cone]

Private Sub Worksheet_Calculate()
If Me.Range("G28").Value = "Yes" Then
MsgBox "Yes"
End If
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"GTVT06" <[email protected]>
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.

 

[GTVT06]

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