how to run a macro automatically when some cell value change?

[jb]

hi all experts,
i have written a macro which is working properly when I run it manually.
it is a formatting related macro.
I want to run this macro automatically when some cell value change.
Help required.

 

[vijay.vizzu]

Dear Jb,

You can call your macro by putting your code in worksheet object. In VBE, just double click on your desired worksheet in left side panel and then right side a combo box contain General, change it to worksheet. Just have a try and revert

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Please put your macro here
   
End Sub

 

[GerryPerry]

Hi JB
Just a word of warning using the Selectionchange event. It works great BUT if your macro actually will fire the event DURING the event then it will spin into outer space. No issues to stop this.. but just look out for it.

To fix it perhaps define a variable as global ( say Global norepeat as Boolean ) and then test for it ( being true ) and exit at the entry to the event . if not true then SET it to true while your macro is running and set it to false after.
Easier to code than describe but I'm sure you get the idea

Gerry

 

[Luke M]

Hi @GerryPerry
The easier option would be to turn off EnableEvents during code run. In answer to JB's original post, don't need to call macro after a selection change, just after an actual change.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False 'No screen flashes, run faster
Application.EnableEvents = False 'No circular calls

Call NameOfMacro 'Change as appropriate

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[GerryPerry]

Hi
Totally correct of course Unless, that is, actions in the required macro are also required to fire events. I was just covering that particular eventuality... but, again, your assumption is a correct one.

Gerry