How do get a macro to autorun when i change a cell in the spreadsheet?

[Infinity]

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.

Thanks in advance.

[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
--
HTH...

Jim Thomlinson


"Infinity" wrote:

>
> 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.
>
> Thanks in advance.
>
>
> --
> Infinity
> ------------------------------------------------------------------------
> Infinity's Profile: http://www.excelforum.com/member.php...o&userid=32725
> View this thread: http://www.excelforum.com/showthread...hreadid=526617
>
>

[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 = False
End Sub

[Infinity]

That was quick... Thanks guys. Will try it out now.

[Greg Wilson]

Mudraker had a slight oversight. The last line of mudraker's code should be:
Application.EnableEvents = True

Regards,
Greg

"Infinity" wrote:

>
> That was quick... Thanks guys. Will try it out now.
>
>
> --
> Infinity
> ------------------------------------------------------------------------
> Infinity's Profile: http://www.excelforum.com/member.php...o&userid=32725
> View this thread: http://www.excelforum.com/showthread...hreadid=526617
>
>

[mudraker]

Greg

Thanks for picking up my mistake - silly me forgot to change setting from False to True after copying & pasting that line of code