Worksheet_change event handler changes a cell; how to stop it triggering itself?

user3272854

How can I make the below the code work? The problem is that when the event handler changes either of the values on the sheet, it is then triggered again.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dd As String
dd=0

If IsEmpty(Target) Then Exit Sub
If Target.Name Like "*.colName" Then           
  ActiveWorkbook.Names("CN\" & dd & "\CNA.").RefersToRange.Value =  Range("CNS").Value
  ActiveWorkbook.Names("CN\" & dd & "\CNJ.").RefersToRange.Value =  Format(Now(),"MM/DD/YYYY")
End If

End Sub

Please help

aucuparia

If you have a Worksheet_Change event handler that itself changes values on the worksheet then it will trigger the worksheet change event again at that step, running the event handler again, and ending up in a loop of recursive calls. This is generally not what you want to happen!

To avoid this, set Application.EnableEvents to False at the start of the event handler. This prevents your code from triggering any other events:

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  On Error GoTo ErrHandler

  'Your code here

  Application.EnableEvents = True
  Exit Sub
ErrHandler:
  Application.EnableEvents = True
End Sub

Note that you need to set EnableEvents back to True at the end of the event handler, and it's also good practice to do this in an error handler so that it is set to true even if the sub fails for any reason.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Worksheet_change event handler changes a cell; how to stop it triggering itself?

From Dev

How to change cell value using Worksheet_change event without triggering a second call

From Dev

How to manually modify a cell that is being edited in a worksheet_change event?

From Dev

Avoid Worksheet_Change event if user changes a certain value

From Dev

How to stop function in event handler

From Dev

Stop the handler inside itself

From Dev

Worksheet_change not working when cell content changes via VBA but does manually

From Dev

Worksheet_change not working when cell content changes via VBA but does manually

From Dev

How to stop Scroll changed event from triggering on data binding (WPF)

From Dev

Stop Triggering Event on Page Load

From Dev

Stop Triggering Event on Page Load

From Dev

how to stop execution of click event from onchange event handler

From Dev

HTML onclick handler not triggering innerHTML changes

From Dev

How to create DOM element on event, and then prevent event handler from triggering until DOM element exists?

From Dev

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

From Dev

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

From Dev

Setting a radio button with .prop() is not triggering event handler

From Dev

Setting a radio button with .prop() is not triggering event handler

From Dev

Can't stop Combobox_Change event VBA triggering another Combobox_Change event

From Dev

How to execute Macro code inside a module from a worksheet_change event

From Dev

How to stop multiple event handler onclick on html from servise resnonse?

From Dev

How to stop OCaml garbage collecting my reactive event handler?

From Dev

How to stop OCaml garbage collecting my reactive event handler?

From Dev

stop blur event from triggering as a result of keypress

From Dev

Stop event propagation on click handler

From Dev

Validate user entry on Worksheet_Change event

From Dev

Stop a 'worksheet_change' or worksheet_calculate from running

From Dev

How do I stop triggering my event-based VBA code when there's an error in the spreadsheet?

From Dev

How to change passed variable inside event handler

Related Related

  1. 1

    Worksheet_change event handler changes a cell; how to stop it triggering itself?

  2. 2

    How to change cell value using Worksheet_change event without triggering a second call

  3. 3

    How to manually modify a cell that is being edited in a worksheet_change event?

  4. 4

    Avoid Worksheet_Change event if user changes a certain value

  5. 5

    How to stop function in event handler

  6. 6

    Stop the handler inside itself

  7. 7

    Worksheet_change not working when cell content changes via VBA but does manually

  8. 8

    Worksheet_change not working when cell content changes via VBA but does manually

  9. 9

    How to stop Scroll changed event from triggering on data binding (WPF)

  10. 10

    Stop Triggering Event on Page Load

  11. 11

    Stop Triggering Event on Page Load

  12. 12

    how to stop execution of click event from onchange event handler

  13. 13

    HTML onclick handler not triggering innerHTML changes

  14. 14

    How to create DOM element on event, and then prevent event handler from triggering until DOM element exists?

  15. 15

    Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

  16. 16

    Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

  17. 17

    Setting a radio button with .prop() is not triggering event handler

  18. 18

    Setting a radio button with .prop() is not triggering event handler

  19. 19

    Can't stop Combobox_Change event VBA triggering another Combobox_Change event

  20. 20

    How to execute Macro code inside a module from a worksheet_change event

  21. 21

    How to stop multiple event handler onclick on html from servise resnonse?

  22. 22

    How to stop OCaml garbage collecting my reactive event handler?

  23. 23

    How to stop OCaml garbage collecting my reactive event handler?

  24. 24

    stop blur event from triggering as a result of keypress

  25. 25

    Stop event propagation on click handler

  26. 26

    Validate user entry on Worksheet_Change event

  27. 27

    Stop a 'worksheet_change' or worksheet_calculate from running

  28. 28

    How do I stop triggering my event-based VBA code when there's an error in the spreadsheet?

  29. 29

    How to change passed variable inside event handler

HotTag

Archive