Subroutine restarts for no reason, causing an infinite loop - Excel (2010) crashes...

[Nico van Rensburg]

Hi there. I am rather experienced in VBA, but came accross a rather weird situation. I have a document with multiple sheets. The subroutine checks the value of a spesific cell on the one sheet and if is not zero, it copies the value from that cell and paste it into a cell on another sheet, then return to the currently active sheet and perform some more actions... That is what is supposed to happen, but does NOT.

What DOES happen an is mind boggling to me is:
It checks the value of cell "G31" sheet "Item 1", finds that is not zero (123.45), copies the value ant paste it into cell "E25" sheet "Quote" AND....... restart from the beginning... copy the value to cell "E25" sheet "Quote" and restarts again and again...

An infinite loop that crashes Excel...

Here is part of the code and the offensive lines is highlighted...

(The value of cell "E25" sheet "Quote" does get changed to 123.45)

Static Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim aSheet As Worksheet
Dim SheetQ As Worksheet

Set aSheet = ActiveSheet
Set SheetQ = Worksheets("Quote")

Application.ScreenUpdating = False
With aSheet

' MsgBox aSheet.Name
If aSheet.Name = "Quote" Then Exit Sub

If aSheet.Name = "Item1" Then
SheetQ.Unprotect Password:="ABCDE"
If Target.Address = "$B$1" Then
SheetQ.Range("B25").Value = Target
End If
If Not (aSheet.Range("G35").Value = 0) Then
Worksheets(aSheet.Name).Range("G35").Copy
' SheetQ.Range("E25").PasteSpecial (xlPasteValues)
SheetQ.Range("E25").Value = .Range("G35").Value
' At this point it jumps right back to the beginning...
SheetQ.EnableSelection = xlUnlockedCells
SheetQ.Protect Password:="ABCDE"
Exit Sub
End If
ElseIf aSheet.Name = "Item2" Then
SheetQ.Unprotect Password:="ABCDE"
If Target.Address = "$B$1" Then
SheetQ.Range("B26").Value = Target
End If
If Not (aSheet.Range("G35").Value = 0) Then
Worksheets(aSheet.Name).Range("G35").Copy
' SheetQ.Range("E26").PasteSpecial (xlPasteValues)
SheetQ.Range("E26").Value = .Range("G35").Value
SheetQ.EnableSelection = xlUnlockedCells
SheetQ.Protect Password:="ABCDE"
Exit Sub
End If


Can someone please shed some light on this subject and relief me from my frustration? Thank you in advance...

 

[MARK858]

I've tried all possible methods of copying... All of them worked...

In the situation that they all worked then you should always choose the

.value = .value

method as it is far and away the most efficient as it doesn't use the clipboards (obviously it isn't an option if you need the formats but you don't in this case)

 

[Nico van Rensburg]

Agreed.