Excel locks up

  • Thread starter Thread starter David Turner
  • Start date Start date
D

David Turner

I use the following for auto check entry in a workbook using XL2000:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Count > 1 Then Exit Sub
If Target.Column = 5 And Target.Value > 1 Then
MyChks = Mid(Target.Formula, 2)
Range("I3").End(xlDown).Offset(1, 2).Value = "Food City"
If Not Target.HasFormula Then
Range("I3").End(xlDown).Offset(0, 4).Value = Target.Value
GoTo OneCheck
End If
v1 = Split(MyChks, "-"): v2 = Split(MyChks, "+")
If InStr(MyChks, "-") > 0 And InStr(MyChks, ")+") = 0 Then
Check1 = v1(LBound(v1))
Range("I3").End(xlDown).Offset(0, 4).Value = Check1
GoTo OneCheck
End If
If InStr(MyChks, "+") > 0 And InStr(MyChks, "-") = 0 Then
Check1 = v2(LBound(v2))
Check2 = v2(UBound(v2))
GoTo TwoChecks
End If
If InStr(MyChks, "+") > 0 And InStr(MyChks, "-") > 0 Then
Check1 = v1(LBound(v1))
Check2 = v2(UBound(v2))
TwoChecks:
Application.ScreenUpdating = True
Range("I3").End(xlDown).Offset(0, 4).Value = Check1
Range("I3").End(xlDown).Offset(1, 4).Value = Check2
Range("I3").End(xlDown).Offset(1, 2).Value = "Food City"
Range("I3").End(xlDown).Offset(0, 2).Select
If MsgBox("Was 2nd Check to Food City?", vbQuestion + vbYesNo, "Payee") =
vbNo Then
ActiveCell.Value = InputBox("Enter New Payee", "New Payee")
End If
OneCheck:
MsgBox "Check for $" & Range("I3").End(xlDown).Offset(0, 4).Value & _
" was #" & Range("I3").End(xlDown).Offset(0, 1).Value
ActiveSheet.Columns(1).Find(Format(CLng(Date), "d")).Offset(1, 1).Select
End If
End If
If Intersect(Target, Range("K5:K34")) Is Nothing Then Exit Sub
On Error GoTo Quit
Target.Offset(0, 2).Select
If Not IsEmpty(Target) And Target.Value > 1 Then
Application.EnableEvents = False
Target.Offset(0, -2).Value = Date
Target.Offset(0, -1).Value = Application.WorksheetFunction.Max(Range
("J:J")) + 1
End If
Quit:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

I just discovered today that if I attempt any Copy/Paste operation using
the right-click context sensitive menu anywhere on the sheet, Excel
freezes and I have to close Excel to recover. I can't even close the
sheet. I exported then removed the code as a test, and no lockup. This
doesn't happen in other workbooks. Nor does it happen if I use the
keyboard's Ctrl+C/Ctrl+V combo or the main Menu's Edit Copy/Edit Paste
with the code in place. It occurs under Win2000 or WinXP on different
systems with different mice.

Can anyone spot anything that might trigger this behavior?
 
David Turner wrote
I just discovered today that if I attempt any Copy/Paste operation using
the right-click context sensitive menu anywhere on the sheet, Excel
freezes
Click to expand...

Actually, Excel doesn't freese as I originally thought, only the workbook.
 
David Turner wrote
I use the following for auto check entry in a workbook using XL2000:
Click to expand...

Ok, I've isolated it to this part of the code. Commenting it out allows the
mouse Copy/Paste

If Intersect(Target, Range("K5:K34")) Is Nothing Then Exit Sub
On Error GoTo Quit
Target.Offset(0, 2).Select
If Not IsEmpty(Target) And Target.Value > 1 Then
Application.EnableEvents = False
Target.Offset(0, -2).Value = Date
Target.Offset(0, -1).Value = Application.WorksheetFunction.Max(Range
("J:J")) + 1
End If

This is basically a second change event that gets triggered either by the
first or by entering something in K5:K34. I've read that having two change
events is not possible, but this has been working flawlessly for about a
year now. It wasn't until today that I even tried a copy paste, AFAIK.
 
Back
Top