Nico van Rensburg
New Member
- Joined
- Feb 21, 2015
- Messages
- 10
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...
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...
Last edited: