Macro entering an infinite loop

[JonABurgess]

I am trying to run a simple copy and paste macro when a cell changes
from 0 to 1. I have the following code for my worksheet:

Private Sub Worksheet_Calculate()

Static OldVal As Integer
If OldVal = 0 Then
If Range("E5").Value = 1 Then
Call test
End If
End If
OldVal = Range("E5").Value

End Sub


This code should just run the macro called "test" when E5 changes from
0 to 1.
My "test" macro is a module in my workbook:


Sub test()

Dim rng As Range

Set rng = Workbooks("Data").Sheets("Limits") _
.Cells(Rows.count, 1).End(xlUp)(2).Offset(3, 0)

Sheets("Form").Select
Range("A3:B5").Copy
rng.PasteSpecial xlFormats
rng.PasteSpecial xlValues

Application.CutCopyMode = False

End Sub

This should copy cells A3 to B5 and paste formats and values into the
"Limits" sheet 3 rows down from the last entry. The test macro runs
fine when I just run it manually. However, when cell E5 changes, the
code seems to run into an infinite loop. It pastes the formats but then
just seems to switch back and forth between the 'limits' and 'form'
worksheets. Anyone know why this could be? I just want the macro to
run once, then stop. Thanks for any help you can give.

 

[Bob Phillips]

It is probably cascading events, the calculate event gets called, which
changes the sheet, which calls the calculate event, which changes the sheet,
.....

Try

Private Sub Worksheet_Calculate()

Static OldVal As Integer
On Error Goto ws_exit
Application.EnableEvents = False
If OldVal = 0 Then
If Range("E5").Value = 1 Then
Call test
End If
End If
OldVal = Range("E5").Value

ws_exit:
Application.EnableEvents = True
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jon]

Bob,

That works! I think you were right, when the formats were pasted the
sheet recalculates because there was a change. Thus the macro starts
over and so on. Your code worked great for me, thanks for the quick
reply.