How can I automatically execute a macro if there a 1 or -1 in a cell

[arrautxa]

I have the following problem.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I do have a worksheet where I have several functions. One of those sub is as follows:<o></o>
<o></o>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)<o></o>
If Target.Column = 1 Then
If Target.Row = 6 Then
If Target.Value = 1 Then
'Call macro here
End If
End If
End If<o></o>
<o></o>
if I place manually 1 in cell A,6 and manually click enter, it will call the macro with no problem.<o></o>
What I need help with is that I am planning to place a 1 or -1 on that cell automatically which is sent by another macro but I need help is to check if the is a 1 with the private sub above and need to perform the click automatically without my intervention. Once the macro is confirmed to be executed I would like to blank that cell in order to wait for the next instruction.
A,6 will not be the only cell that will have to check for a 1 but the check will always be on column A.
Can anybody help me with this macro.
Best Regards
Bill

 

[Andrew Poulsom]

If another macro is changing the cell why do you need an event procedure?

 

[arrautxa]

Hello Andrew. Thanks for responding so soon. (3AM wao)
I am using
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

I have a simplistic code that when there is a particular number lets say 1 or -1 in a particular cell and I press enter it fires the macro no problem.What I don't know how to do it is when I have a formula on that particular cell and generate a value, Worksheet_Change does not seems to recognize it as a value but instead as a formula and don't execute anything.

I try Worksheet_Calculate but it enter in a loop because other changes occuring in other worksheet on the same workbook.I didnt know how to use that function for a specific worksheet or a particular range of cells.(is it possible?)
Could you help me . Thanks in advance for your great help. I already PM with the question and the code I am using.
Regards
Bill

 

[Andrew Poulsom]

The Worksheet_Calculate event procedure doesn't have a target argument. If you want to avoid it being called repeatedly put:

Application.EnableEvents = False

at the beginning of the procedure and:

Application.EnableEvents = True

at the end.

Maybe you can use the cell's precedent cell in the Worksheet_Change event procedure. What's the formula that it contains?

 

[arrautxa]

First of all Thanks Andrews for your help.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
With you suggestion I look up Internet and found the following: Bellow in red<o></o>
<o></o>
<o></o>
<o></o>
What I am trying to accomplish is: If there is a formula on a range lets say c5:c300 either or in several non contiguous cells in column A, and the result of that formula<o></o>
Is a 1 or -1 a 1 or -1 will have to be placed on column A. <o></o>
When there is a 1 /-1 in column A, that action will trigger a sub called INSMKTGU if it is 1 and INSCLOSEMKTGU if it -1<o></o>
Then once the sub has been triggered I need to clear column A.<o></o>
<o></o>
The problem: <o></o>
At this moment the way I am doing it is working but neither efficiently nor correctly. Whenever there is a 1 product of the formula in column C, a 1 or -1 will appear on A (that is what I am expecting to happen).<o></o>
But instead of triggering sub INSMKTGU once it triggered several times.
Following the result of the formula on column C will be 0 so I clear the content on A. This clearing takes to much time and I believe is because the loop is to long.<o></o>
Could you please help me to restrict INSMKTGU or INSCLOSEMKTGU for being triggered more than one at a time and to clear the content on column A once either subs has been triggered.<o></o>
I will PM with the excel worksheet so you don't have to replicate what I am writing above.
Thanks a lot in advance.<o></o>
Bill<o></o>
<o></o>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)<o></o>
Dim keyCells As Range<o></o>
<o></o>
Set keyCells = Range("c5"): Rem cell With formula<o></o>
<o></o>
On Error Resume Next<o></o>
Set keyCells = Application.Union(keyCells, keyCells.Precedents)<o></o>
On Error GoTo 0<o></o>
<o></o>
If Not Application.Intersect(Target, keyCells) Is Nothing Then<o></o>
MsgBox "Cell c5, containing a formula, has been changed."<o></o>
End If<o></o>
<o></o>
<o></o>
<o></o>
If Target.Value = 1 Then<o></o>
change'This is the sub that clear content on column A and place the result of the formula en C in Column A.<o></o>
End If<o></o>
<o></o>
<o></o>
<o></o>
If Target.Value <> 0 Then<o></o>
change'This is the sub that clear content on column A and place the result of the formula en C in Column A.<o></o>
End If<o></o>
<o></o>
If Target.Value > 1 Then<o></o>
change'This is the sub that clear content on column A and place the result of the formula en C in Column A.<o></o>
End If<o></o>
<o></o>
'the following will trigger when there is a 1 or -1 in column A<o></o>
<o></o>
If Target.Column = 1 Then<o></o>
If Target.Row = 5 Then<o></o>
If Target.Value = 1 Then<o></o>
INSBUYMKTGU<o></o>
End If<o></o>
End If<o></o>
End If<o></o>
If Target.Column = 1 Then<o></o>
If Target.Row = 5 Then<o></o>
If Target.Value = -1 Then<o></o>
INSCLOSEBUYMKTGU<o></o>
End If<o></o>
End If<o></o>
End If<o></o>
<o></o>
<o></o>
Private Sub change()<o></o>
'Private Sub Worksheet_Calculate()<o></o>
'Dim lRtn As Long<o></o>
'lRtn = OrderForm.fctGetMainBidAsk()<o></o>
<o></o>
Dim i As Long, Srow As Long, ERow As Long, Intrvl As Long<o></o>
Srow = 5 'Starting Row<o></o>
ERow = 350 'Ending Row<o></o>
Intrvl = 1 'Interval<o></o>
<o></o>
For i = Srow To ERow Step Intrvl<o></o>
Select Case Range("c" & i).Value<o></o>
'Case 1<o></o>
'Range("A" & i).ClearContents<o></o>
'Case ""<o></o>
'Range("A" & i).ClearContents<o></o>
'Range("A" & i).Value = 0<o></o>
Case 0<o></o>
Range("A" & i).ClearContents<o></o>
'Range("A" & i).Value = 0<o></o>
Case Is > 0<o></o>
If Len(Range("A" & i)) >= 0 Or Len(Range("A" & i)) = "" Or Len(Range("A" & i)) < 0 Then<o></o>
Range("A" & i).ClearContents<o></o>
'Range("A" & i).Value = 0<o></o>
Range("A" & i).Value = Range("B" & i).Value<o></o>
End If<o></o>
Case Is = -1<o></o>
If Len(Range("A" & i)) <= 0 Or Len(Range("A" & i)) = "" Or Len(Range("A" & i)) > 0 Then<o></o>
'Range("A" & i).ClearContents<o></o>
'Range("A" & i).Value = 0<o></o>
Range("A" & i).Value = Range("B" & i).Value<o></o>
End If<o></o>
End Select<o></o>
Next i<o></o>
'End Sub<o></o>
End Sub

 

[Andrew Poulsom]

Have you tried disabling events at the beginning of your code like I suggested in post #4?

 

[arrautxa]

Yes sir I did what you suggested on post #4 and it partially works because seems to put a 1 or -1 only one time but now when a 1 or -1 appear on cell 5.1 do not trigger the sub that suppose to call but if you manually write a 1 or -1 on that cell and hit enter the sub is called as it suppose to do it.
Thanks in advance
Bill

 

[arrautxa]

Andrew is there any way that I could send you PM with the workwook so you could evaluate what is the problem??
Thanks in advance
Bill

 

[Andrew Poulsom]

Please post your current code. When doing so please use code tags:

http://www.mrexcel.com/forum/misc.php?do=bbcode#code

 

[arrautxa]

Re: how can I trigger a macro when a range of cells in workbook XX are link to range of cells in another workbook YY

Andrew This similar of what I posted above but now I would like to know
if you could help me with this:
how can I trigger a macro when a range of cells in workbook XX are link to range of cells in another workbook YY and either of these cells changes from 0 to either -1 or +1 without me interacting.

Depending which cell in sheetXX within the specify range in workbook XX changes it will have to trigger a particular macro.

On Sheetyy in Workbook YY there are going to be series of calculations.Depending on those calculations there will be a 1 or -1 in column A Since this column range is linked to SheetXX in WorkbookXX
whenever there is a change in any of the cells it will trigger the macro that correspond.
change in cell A5 will execute macro1
change in cell A6 will execute macro 2
change in cell A9 will execute macro 3
change in cell A10 will execute macro 4
and so on.

The code in earlier post kind of work if I click enter the code bellow king of work if I click on thenworkbook Ineed help trigering different macros without me intervening.

I am trying the following but it seems that because of Worksheet_Calculate() which aparentlyis the one detecting the change and triggering.You will see the workbook flashing and will not show the message until I click on the workbook.
The problem with this approach is that if I have more calculations or changes going on in other worksheet in the same workbook it will enter in a loop.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
If Range("A1").Value = 1 Then
'MyA2
'MyA2 = Range("A1").Value
MsgBox "Hello - A1 changed"
End If
'End If

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A1:A1")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

If Range("A1") = 1 Then
MsgBox "Cell " & Target.Address & " has changed."
End If
End If


End Sub
Private Sub Worksheet_Calculate()
If Range("A2").Value = 2 Then
'MyA2
MyA2 = Range("A2").Value
MsgBox "Hello - A2 changed"
End If
If Range("A1").Value = 1 Then
'MyA2
MyA2 = Range("A1").Value
MsgBox "Hello - A1 changed"
End If
End Sub