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","DoNothi ng")
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!

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?

"SJW_OST" wrote:

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","DoNothi ng")
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!

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?


"Charlie" wrote:

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?

"SJW_OST" wrote:

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","DoNothi ng")
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!

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.


SJW_OST wrote:
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","DoNothi ng")
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!

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
--
HTH...

Jim Thomlinson


"SJW_OST" wrote:

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","DoNothi ng")
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!