Atholl
Active Member
- Joined
- May 19, 2002
- Messages
- 436
Hello all,
The code below performs a function if any cell in the range G5:G1000 is altered, and it works very well. However, sometimes I need to delete rows which intersect the range. When I do this, Excel hangs and the only way to exit this is to click to 'X' in the outermost top right of the screen to exit Excel, then when it asks if I want to save changes, I click cancel and things are back to normal. Can anyone offer any suggestions to avoid the hang up when a row is deleted?
Atholl
The code below performs a function if any cell in the range G5:G1000 is altered, and it works very well. However, sometimes I need to delete rows which intersect the range. When I do this, Excel hangs and the only way to exit this is to click to 'X' in the outermost top right of the screen to exit Excel, then when it asks if I want to save changes, I click cancel and things are back to normal. Can anyone offer any suggestions to avoid the hang up when a row is deleted?
Atholl
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
On Error GoTo err_fix
If Not Intersect(Target, Range("G5:G1000")) Is Nothing Then
If Target.Value = "ATM" Then MsgBox "Please ensure that Task is allocated", vbInformation, "ATR"
End If
Application.ScreenUpdating = True
Exit Sub
err_fix:
If Err.Number = 13 Then
Exit Sub
Else
MsgBox Err.Number & ": " & Err.Description
End If
Application.ScreenUpdating = True
End Sub