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

G

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 the cell that feeds into G28's formula manually, however
the cell that feeds into "G28" a formula, and wont be keyed in
manually, 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.
 
J

Jim Thomlinson

That code will have a problem if any of the dependant of the formula in G28
are not on this sheet. Target.Dependents only returns the dependent cells on
the current sheet. Just one possible reason things might not be working...
 
J

Jim Cone

See another response in your identical post in the public.excel group.
--
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 the cell that feeds into G28's formula manually, however
the cell that feeds into "G28" a formula, and wont be keyed in
manually, 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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top