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...

 

[jolivanes]

Nico
Would you be so kind and put code tags around your code first.
This is required on this forum.

 

[Nico van Rensburg]

Hi Jolivanes

Thanx for your prompt reply and sincere apologies. Code follows:


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
                                     [COLOR=#ff0000]Worksheets(aSheet.Name).Range("G35").Copy[/COLOR]
                     '                    SheetQ.Range("E25").PasteSpecial (xlPasteValues)
                                 [COLOR=#ff0000]SheetQ.Range("E25").Value = .Range("G35").Value[/COLOR]
                     [COLOR=#006400]'     At this point it jumps right back to the beginning...[/COLOR]
                                     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
                 .
                 .
                 .
                 .                     .
                 .

 

[GooberTron]

Have you tried;

Application.EnableEvents = False

at the beginning of the routine and then;

Application.EnableEvents = True

at the end?

 

[Nico van Rensburg]

Hi GooberTron

I have NOT tried what you've suggested, but seem to have solved the problem. It does not make sense though... This macro worked perfectly in Excel 2013, but since the lightning got a liking in my computer and blew the power supply and motherboard, I had to go back to an old Dell C810 laptop with Windows XP SP 2. I installed Office 2007 and upgraded to Office 2010, since -2013 requires Windows 7 and up. Therefor I had to make a few changes to the macro (downgrade it) 'cause it won't run so well in -2010. That's where my problems started. Thanx for your suggestion, I will surely give it a try...

The solution:

The routine runs in the SheetChange event. It seems like once the value is changed, it automatically triggers the SheetChange event, which runs the same code again, which on its turn re-triggers the SheetChange event an so on and so on... Here is the altered code: Changes is highlighted...

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:="D@nger"
            If Target.Address = "$B$1" Then
                SheetQ.Range("B25").Value = Target
            End If
            If Not (aSheet.Range("G35").Value = 0) Then
                [I][B][COLOR=#ff0000]If Not (aSheet.Range("G35").Value = Target.Value) Then
                       Worksheets(aSheet.Name).Range("G35").Copy
                       SheetQ.Range("E25").PasteSpecial (xlPasteValues)[/COLOR][/B][/I]
                [COLOR=#ff0000][I][B]End If[/B][/I][/COLOR]
                SheetQ.EnableSelection = xlUnlockedCells
                SheetQ.Protect Password:="D@nger"
                Exit Sub
            End If
         ElseIf aSheet.Name = "Item2" Then

Thanx to everyone who looked at my code and still are breaking their heads... Bye for now!

 

[GooberTron]

Yeah I didn't really read through your code properly - but the principle you hit on is correct (and expected - not sure why you would see a difference between 2010 and 2013). If you are using "Event" code you need to be mindful of triggering new events and ending up in a recursive endless loop (i.e. the procedure effectively keeps calling itself again). Hence my suggestion to turn off event handling at the beginning and back on again at the end, to stop this kind of problem.

 

[Nico van Rensburg]

Come to think of it... I think I DID use this method of Event enabling and -disabling in one of my older macros and workbooks. Iv'e forgotton about it already. That book is also no more in use.

 

[MARK858]

This bit

Worksheets(aSheet.Name).Range("G35").Copy
                       SheetQ.Range("E25").PasteSpecial (xlPasteValues)

was much better written as

SheetQ.Range("E25").Value = .Range("G35").Value

without the copy line which could/should have been commented out in original code

 

[Nico van Rensburg]

Hi Mark

I agree with you. Point is, I've tried all possible methods of copying... All of them worked... yet it still kept on running in an endless loop. But, thank goodness, it is all sorted now.

 

[Nico van Rensburg]

An afterthought...

Just as a matter of interest... The Dell Latitude C810 laptop that I mentioned earlier, is older than seven years. It has been standing, unused for longer than four years. I've started it up, upgraded XP to SP3, Office 2003 to -2010, disabled a couple of services and startup programs, cleaned out all the crap and temp files and you wont believe how well it still performs. And it has only 256 Mb memory on top of it. After standing for so long, the battery is still working perfectly. I have'nt tested it thoroughly though, so I can't tell for how long it will run on battery power. Bottom line is, she's still running sweetly. Dell has certainly earned my respect.

Hats off to Dell!!