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

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

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

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

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

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


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=526617

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

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

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

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

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

Greg

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


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=526617