VBA Change event code

[jamescooper]

Hello, the following code populates the codes of cell K9 if it is changed in the next available row in column AL and K10 in the next available in AV and F4 in the next available in AK.

However, this only happens if the cell is changed manually by entering in. My excel spreadsheet is linked to software where the cell can change without this happening; any idea how I would adapt my code to achieve any changes?

Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim nR As Long


nR = WorksheetFunction.Max(2, Sheets("Bet Angel").Range("AL" & Rows.Count).End(xlUp).Row + 1)


If Not Intersect(Target, Sheets("Bet Angel").Range("K9:K10")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Sheets("Bet Angel").Range("K9").Copy Destination:=Sheets("Bet Angel").Range("AL" & nR)
Sheets("Bet Angel").Range("K10").Copy Destination:=Sheets("Bet Angel").Range("AV" & nR)
Sheets("Bet Angel").Range("F4").Copy Destination:=Sheets("Bet Angel").Range("AK" & nR)


End If


End Sub

 

[jamescooper]

It has worked, but immediately comes up with run time error 28 - out of stack space?

Any ideas?

thanks.

 

[offthelip]

that is probably because copying the cells across is causing another worksheet calculate event to trigger and so it gets in a loop, try turning off the event before the copies and turning it back on again afterwards see below:

Private Sub Worksheet_Calculate()
nr = Cells(Rows.Count, "AL").End(xlUp).Row + 1
inarr = Range("K9:K10")
If inarr(1, 1) <> oldk9 Or inarr(2, 1) <> oldk10 Then


[COLOR=#ff0000]Application.EnableEvents = False[/COLOR]
Range("AL" & nr) = inarr(1, 1)
Range("AV" & nr) = inarr(2, 1)
oldk9 = inarr(1, 1)
oldk10 = inarr(2, 1)
Sheets("Bet Angel").Range("F4").Copy Destination:=Sheets("Bet Angel").Range("AK" & nr)
[COLOR=#ff0000]Application.EnableEvents = True[/COLOR]


End If




End Sub

 

[jamescooper]

that is probably because copying the cells across is causing another worksheet calculate event to trigger and so it gets in a loop, try turning off the event before the copies and turning it back on again afterwards see below:

Private Sub Worksheet_Calculate()
nr = Cells(Rows.Count, "AL").End(xlUp).Row + 1
inarr = Range("K9:K10")
If inarr(1, 1) <> oldk9 Or inarr(2, 1) <> oldk10 Then


[COLOR=#ff0000]Application.EnableEvents = False[/COLOR]
Range("AL" & nr) = inarr(1, 1)
Range("AV" & nr) = inarr(2, 1)
oldk9 = inarr(1, 1)
oldk10 = inarr(2, 1)
Sheets("Bet Angel").Range("F4").Copy Destination:=Sheets("Bet Angel").Range("AK" & nr)
[COLOR=#ff0000]Application.EnableEvents = True[/COLOR]


End If




End Sub

Works really well now thank you.