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
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.
Comments