When I change a cell a macro will autorun BUT..

I initially posted a thread on "How do get a macro to autorun when i change a cell in the spreadsheet?"

Ok, here come the problem now, I replicated this worksheet in the same workbook to do another analysis. Now i have 2 worksheets namely CountryA and CountryB (Copied via using create copy of worksheet).

So when I change the cell in CountryA, the macro is also running CountryB. The Cell which I change is actually a Validation list of "Purchase" and "Sales". I change it let's say from Purchase to Sales. It goes to the CountryB worksheet and runs the macro???

Why is this Private Sub Worksheet_Change(ByVal Target As Range) also performing the macro on CountryB worksheet?

How can make sure that it is only running for CountryA when I change the cell in this worksheet?

Is it something wrong i did?

Please help... and Thanks in advance

Below was the reply that i have got previously

PHP Code: 

 Question : Let say Cell "A1" has only 2 values that is Purchase or Sales.
Now when i change this cell to either value, i want it to run a macro
e.g. When i change Cell to "Purchase" i want to autorun Macro A and When i change Cell to "Sales" i want to autorun Macro B. How do i do this? And it is for a particular worksheet only.

[B]Jim Thomlinson [/B]
Right click the tab of the sheet containing the ceel with purchase and sale
in Cell A1 then select view code... Paste the following.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value = "Purchase" Then Call Macro1
If Target.Value = "Sales" Then Call Macro2
End If
End Sub

[B]Mudraker[/B]
You need to use the worksheet change event
This goes in the module for the particular worksheet that you want to trigger the macro

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
Application.EnableEvents = False
Select Case Target.Value
Case "Purchase"
Call macroA
Case "Sales"
Call MacroB
End Select
End If
Application.EnableEvents = True
End Sub 


If the sheetA code changes something on sheetB then that will trigger the
sheetB code to run.

Try disabling events before running the sheet code: re-enable at the end

'***********************
application.enableevents=false
'do stuff
application.enableevents=true
'***********************

Tim

"Infinity" <Infinity.25h31p_1143705302.2274@excelforum-nospam.com> wrote in
message news:Infinity.25h31p_1143705302.2274@excelforum-nospam.com...
>
> I initially posted a thread on *"How do get a macro to autorun when i
> change a cell in the spreadsheet?"*
>
> Ok, here come the problem now, I replicated this worksheet in the same
> workbook to do another analysis. Now i have 2 worksheets namely
> CountryA and CountryB (Copied via using create copy of worksheet).
>
> So when I change the cell in CountryA, the macro is also running
> CountryB. The Cell which I change is actually a Validation list of
> "Purchase" and "Sales". I change it let's say from Purchase to Sales.
> It goes to the CountryB worksheet and runs the macro???
>
> Why is this *Private Sub Worksheet_Change(ByVal Target As Range)* also
> performing the macro on CountryB worksheet?
>
> How can make sure that it is only running for CountryA when I change
> the cell in this worksheet?
>
> Is it something wrong i did?
>
> Please help... and Thanks in advance
>
> Below was the reply that i have got previously
>
>
> PHP code:
> --------------------
> Question : Let say Cell "A1" has only 2 values that is Purchase or
> Sales.
> Now when i change this cell to either value, i want it to run a macro
> e.g. When i change Cell to "Purchase" i want to autorun Macro A and When
> i change Cell to "Sales" i want to autorun Macro B. How do i do this? And
> it is for a particular worksheet only.
>
> JIM THOMLINSON
> Right click the tab of the sheet containing the ceel with purchase and
> sale
> in Cell A1 then select view code... Paste the following.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$A$1" Then
> If Target.Value = "Purchase" Then Call Macro1
> If Target.Value = "Sales" Then Call Macro2
> End If
> End Sub
>
> MUDRAKER
> You need to use the worksheet change event
> This goes in the module for the particular worksheet that you want to
> trigger the macro
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$A$2" Then
> Application.EnableEvents = False
> Select Case Target.Value
> Case "Purchase"
> Call macroA
> Case "Sales"
> Call MacroB
> End Select
> End If
> Application.EnableEvents = True
> End Sub
> --------------------
>
>
> --
> Infinity
> ------------------------------------------------------------------------
> Infinity's Profile:
> http://www.excelforum.com/member.php...o&userid=32725
> View this thread: http://www.excelforum.com/showthread...hreadid=527970
>

Sorry, but i still dont understand . I am not very good at macro.

Would appreciate very much if you could give me an example.

Thanks... thanks.. thanks...

WTF??

Sometimes it is working, sometimes it is not working.... FRUSTRATED

Without knowing the exact code you're using it's difficult to suggest
anything else.

The modification to Jim's code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents=false
If Target.Value = "Purchase" Then Call Macro1
If Target.Value = "Sales" Then Call Macro2
Application.EnableEvents=false
End If
End Sub


> WTF??

>Sometimes it is working, sometimes it is not working....
> *FRUSTRATED*


> --
> Infinity

Typo:

> If Target.Value = "Purchase" Then Call Macro1
> If Target.Value = "Sales" Then Call Macro2
> Application.EnableEvents=True

--
Tim Williams
Palo Alto, CA


"Tim Williams" <timjwilliams@gmail.com> wrote in message
news:1143747585.467645.271740@v46g2000cwv.googlegroups.com...
> Without knowing the exact code you're using it's difficult to suggest
> anything else.
>
> The modification to Jim's code:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$A$1" Then
> Application.EnableEvents=false
> If Target.Value = "Purchase" Then Call Macro1
> If Target.Value = "Sales" Then Call Macro2
> Application.EnableEvents=false
> End If
> End Sub
>
>
> > WTF??
>
> >Sometimes it is working, sometimes it is not working....
> > *FRUSTRATED*
>
>
> > --
> > Infinity
>

Thanks.... Will test it out today....