I'm working on a simple worksheet, and I need to change some data in the cells depending on the user input; those changes are made using the Worksheet_Change event. However, when I change another cell, the event is triggered again, so it becomes quite a headache (it's kind of a "chicken-and-egg" scenario).
Example:
private sub Worksheet_Change(ByVal Target as Range)
with target ' Only cells in column C are unlocked and available for edition
select case .row
case 4
if .value = 1 then
ActiveSheet.cells(5,3).value = 0
else
ActiveSheet.cells(5,3).value = 1
end if
case 5
if .value = 1 then
ActiveSheet.cells(4,3).value = 0
else
ActiveSheet.cells(4,3) = 1
end
end select
end with
end sub
As you can see, changes in row 4 trigger changes in row 5, which may trigger another change in row 4... and it becomes an "infinite call", which eventually crashes excel.
So, the question is: Is there a way to programmatically change the value of a cell without triggering the Worksheet_Change event?
Disable interrupts while changing cells, then re-enable them when you are done:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range
Set A = Range("A1")
If Intersect(Target, A) Is Nothing Then Exit Sub
Application.EnableEvents = False
A.Value = ""
A.Offset(0, 1).Value = "CLEARED"
Application.EnableEvents = True
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments