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


Formula:
--------------------
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

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" wrote in
message ...

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


Formula:
--------------------
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 a
macro.

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

Thanks... thanks.. thanks...:

--
Infinit
-----------------------------------------------------------------------
Infinity's Profile: http://www.excelforum.com/member.php...fo&userid=3272
View this thread: http://www.excelforum.com/showthread.php?threadid=52797

WTF??

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

--
Infinit
-----------------------------------------------------------------------
Infinity's Profile: http://www.excelforum.com/member.php...fo&userid=3272
View this thread: http://www.excelforum.com/showthread.php?threadid=52797

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" wrote in message
oups.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....


--
Infinity
------------------------------------------------------------------------
Infinity's Profile: http://www.excelforum.com/member.php...o&userid=32725
View this thread: http://www.excelforum.com/showthread...hreadid=527970