MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Forcing a macro to run


Posted by Jamie on March 24, 2000 7:38 PM

Hello all,
I need to setup a macro to run if another cell equals a certain value. I've got the macro made but I don't know how to tell it to run if a particular cell equals a certain value.
So if, for example, A1=5, then I want the macro to run but only if A1 equals only 5.
Any help will be greatly appreciated.
Jamie


Posted by Celia on March 25, 2000 12:13 AM

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


Posted by Jamie on March 25, 2000 12:02 PM

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

Posted by Celia on March 25, 2000 5:55 PM

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


Posted by Ivan Moala on March 25, 2000 9:32 PM

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

Posted by Jamie on March 26, 2000 1:24 PM

Thanks...

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

Posted by Celia on March 26, 2000 3:25 PM

A question for Ivan

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

Posted by Ivan Moala on March 27, 2000 3:16 AM

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

Posted by Jack Terriman on March 27, 2000 8:12 AM

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

Posted by Celia on March 27, 2000 3:13 PM

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

Posted by JA on March 28, 2000 7:00 AM

Re: Works great but one problem

Posted by JACK TERRIMAN on March 28, 2000 7:08 AM

Re: Works great