VBA Macro Repeats and Will Not Stop

Question

Hi All,

My VBA code will not stop. My spreadsheet is being fed by an API. The code is design to trigger on the value of a cell. Before the value to check the code asks if another value is 1, this cell value is inputted by the code and if it equals 1 it stops the code from executing.

The code checks 3 different values, The value of 1 never appears so the code continues to cut and paste. I am sure I have something mixed up or wrong but I am not getting any compile errors or debug so the code looks correct just maybe not in the correct order. I stop the code with application events if the value is 1, problem is the value never becomes 1. I have change around the TRU and False order but it does not work either way. Code below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet: Set ws = Sheet1
If ws.Range("BM1").Value <> 1 Then
Application.EnableEvents = True
End If
If ws.Range("BK1").Value = 10 Then
With Range("BC10:BC68").Copy
Range("BC10").PasteSpecial Paste:=xlPasteValues
Range("BM1") = 1
End With
Range("BM1").Value = 1
Application.EnableEvents = False
End If
If ws.Range("BM2").Value <> 1 Then
Application.EnableEvents = True
End If
If ws.Range("BK2").Value = 5 Then
With Range("BD10:BD68").Copy
Range("BD10").PasteSpecial Paste:=xlPasteValues
Range("BM2") = 1
End With
Range("BM2").Value = 1
Application.EnableEvents = False
End If
If ws.Range("BM3").Value <> 1 Then
Application.EnableEvents = True
End If
If ws.Range("BK3").Value = 105 Then
With Range("BE10:BE68").Copy
Range("BE10").PasteSpecial Paste:=xlPasteValues
Range("BM3") = 1
End With
Range("BM3").Value = 1
Application.EnableEvents = False
End If
End Sub

Many Thanks

Jonathan

Answer 1

Sorry, just a correction, it is not a macro it is VBA code, thanks

Answer 2

Thanks Hans, looks like I had the code in the wrong place, will test again tomorrow

Thanks

Jonathan

Answer 3

Hans,

Same result, BK1 turns to 10 and BM1 = "" but lines 4, 5 and 6 do not execute. I have pasted this code into this workbook, I have deleted the module I inserted and cleared out the code in sheet 1.

Thanks

Jonathan

Answer 4

No compile errors or errors causing debug to run

Answer 5

Hans,

I do not understand but I closed down the spreadsheet after saving, re-opened and it all works perfectly. Many thanks for this, much appreciated.

Thanks

Jonathan