Forcing a macro to run

Jamie
See the following question yesterday on this board and the reply thereto :-
Running Macro using a formula - Jack Terriman 12:12:58 3/23/100
Celia

Works great but one problem

Hi Celia,
thanks for the info...the code you posted in response to "Running Macro using a formula - Jack Terriman 12:12:58 3/23/00" works perfectly except I am having one problem with it. In the following example:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
If Range("A1").Value > 2 Then
Call macro1
End If
End If
End Sub

If I physically input a number greater than 2 into A1, then the macro runs however, I need A1 to be a formula, (i.e. =SUM(A2:A10), and then if the formula results are greater than 2, I want the macro to run. With the current code above and a formula inserted in A1, even if the results are greater than 2, it won't launch the macro.
Would you please explain what I'm doing wrong?
Thanks
Jamie

Re: Works great but one problem

Jamie
You are not doing anything wrong. The Worksheet_Change event will not work if the value is changed by a calculation.
You could try the Worksheet_Calculate event but the procedure will occur whenever any calculation on the worksheet is made (but this should not be a problem if macro1 is not a long one).
There could well be a better way than this of doing what you want :-

Private Sub Worksheet_Calculate()
If Range("A1") > 2 Then
Call Macro1
End If
End Sub

Celia

Re: Works great but one problem

Jamie
As an alternative to Celias and to get you around
this probem try this:

Public OldVal

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldVal

If Range("A1").Value > 2 Then
If Range("A1").Value <> OldVal Then
Call macro1
OldVal = Range("A1").Value
End If
End If
End Sub

This will work and only when your value A1 changes

Ivan

Thanks...

Hey guys,
thanks to you both for helping me out here. Those new formulas work great.
Jamie

A question for Ivan

Ivan
Very nice. A question though :-
Is there a reason to use both 'Public OldVal' and 'Static OldVal' ?
Celia

Re: A question for Ivan

Celia
Actually NO this was a mistake, I was thinking
along another line. You don't actually need
the Public statement - was thinking about something
else concerning Global Variables

Ivan

Re: Works great but one problem

Thanks for respinding to my question.

I just got on line this morning and this is first time that I’ve had a chance to respond to the message.
I’m new at visual basic and I’m unable to get the macro to work. Cell a1 has a formula in it and when
I get cell a1 to show 3 nothing happens. What am I going wrong?

This is Macro1

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 03/27/2000 by JACK TERRIMAN
'
ActiveSheet.Pictures.Insert("F:\JACK.jpg").Select
'
End Sub

Module5

Private Sub Worksheet_Calculate()
If Range("A1") > 2 Then
Call Macro1
End If
End Sub

Re: Works great but one problem

Thanks for respinding to my question. I just got on line this morning and this is first time that I’ve had a chance to respond to the message. Macro1 Macro Macro recorded 03/27/2000 by JACK TERRIMAN

Jack
You have to put the macro in the Worksheet code module (not in a normal module)
Celia

Re: Works great but one problem

Re: Works great