Run code on "IF" formula result change

S

SJW_OST

I have a formula that changes depending on if the calculation result is
between 2 cell results.

=IF(AND($F$28>=E30,$F$28<=G30),"SaveFile","DoNothing")
This formula is in cell F30
E30 = 12:00:00
G30 = 12:20:59

I have a timer that is tied to $F$28 which updates the time periodically.
When the time in $F$28 is between E30 & G30, the "IF" formula changes to
"SaveFile". When this happens I want a macro of my choosing to run.
I have tried inputing the following to the specific sheet the formula is on;

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$30" And Target.Value = "SaveFile" Then
Call MessageBoc
End If
End Sub

But this does not work. I tried changing Target.Value to Target.FormulaR1C1,
I have tried writing a regular module sub, I have tried everything my limited
mind can think of.
How can I get a macro to run when an "IF" formula result changes to
something specified?
Please help, Thank you in advance!
 
C

Charlie

Apparently the IF statement doesn't fire the Change event. Can you put your
code into the Timer function and examine the contents of Range("F30")
whenever the timestamps are updated?
 
S

SJW_OST

Huh? This is the timer code I am using;

Option Explicit
Dim timer_enabled As Boolean
Dim timer_interval As Double
Sub Timer()
' output the current time to cell D12
Sheets("Options").Range("F28").Value = CStr(Time)
End Sub

Sub TimerOn() 'TURN ON THIS TIMER
Dim interval As Double
interval = CDbl(Sheets("Options").Range("F26").Value) ' get the interval
value from cell D7
Call timer_Start(interval) ' start the timer with the specified interval
End Sub
Sub timer_OnTimer()
Call Timer
If timer_enabled Then Call timer_Start
End Sub
Sub timer_Start(Optional ByVal interval As Double)
If interval > 0 Then timer_interval = interval
timer_enabled = True
If timer_interval > 0 Then Application.OnTime (Now + timer_interval),
"Timer_OnTimer"
End Sub

Sub timer_Stop() 'STOP THIS TIMER
timer_enabled = False
End Sub

How do I get the IF statement to fire the Change event?
 
D

Douglas Klimesh

Worksheet_Change only seems to work when a user changes something. Try
using Worksheet_Calculate instead. Note it has no arguments, so it
would look something like this:

Private Sub Worksheet_Calculate()
If Cells(30,6) = "SaveFile" Then
Call MessageBoc
End If
End Sub

You will probably need some check so that it doesn't immediately
repeatedly call MessageBoc.
 
J

Jim Thomlinson

Change does not run as the result of formulas being updated. That leaves you
with 2 options. One is to check the Precedents of F30 and the other is to
check F28, E30 and G30... Give this a try...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F30").Precedents) Is Nothing and
Range("F30").Value = "SaveFile" Then
Call MessageBoc
End If
End Sub
 

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