Code that will automatically run macro when cells change

[BSmith]

I need to put code into the worksheet that will force a macro to run when any one of five cells change (the cells are C4,B8,B9,B14, & B15). The macro is called "Solve". Can someone help me figure out the code that needs to be in place in order to run this macro when any one of those 5 cells change?

 

[fred]

What if you enter the number wrong and macro runs automatically? There is no "undo" once macro is run, if i remember correctly.

 

[mooks]

The code goes into the worksheet change event procedure. In the VBA editor's Project Explorer window, double click the sheet name which has the cells you want to monitor. Then paste the following code. Assuming your 'Solve' macro is currently in a module, this should work:


Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


Select Case Target.Address


Case "$C$4", "$B$8", "$B$9", "$B$14", "$B$15"

'Insert your code here

Call Solve


Case Else

'Insert code for all other cells (if needed, else leave blank)


End Select


End Sub

 

[Luke M]

Just a possible heads up, the Change event macro isn't trigged by the result of a formula changing, or if you select something from a data validation dropdown. That might not apply in this case, but I've run into it before and it caused a few headaches...

 

[BSmith]

Thanks mooks - looks like it works.

 

[mooks]

Great! Glad I could be of help.

 

[smanjunath]

This was also Exactly what i needed,

worked great for me..

Thanks

 

[theine]

Can someone tell me why the following code doesn't work? (This is used in Excel 2010)


------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False


If Target.Address = "$H$493" > 1 Then


InsertSUBpartDRAWINGS


Else


If Target.Address = "$H$493" < 2 Then Exit Sub


End If

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub

------------------------------------------------------------------------


Where the "InsertSUBpartDRAWINGS" is a recorded macro

The code is supposed to check a current cell : if <= 1 then will do the macro otherwise don't do anything.

If the value in the current cell is >1 then run the macro

 

[SirJB7]

Hi, theine!

Would you mind starting a new topic instead of writing and asking over another user's one? This is usually seen as hijacking. Thank you.

Regards!