Hi all,
I have a code that produces a mssg box if any "P" value is selected (data validation) in column A. The mssg box allows the user to specify how many additional lines they need to add, and adds them by copying the line x number of times. However, I often encounter a run-time error that completely freezes excel and forces me to quit excel via task manager. Is there and if error code I could tie around this?
Any help would be greatly appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, nRows As Long
'Application.EnableEvents = False
'Sheets("Step2").Unprotect Password:="**********"
If Not Intersect(Target, Range("A:A")) Is Nothing Then
'Application.EnableEvents = False
'On Error GoTo Whoa
For Each cell In Intersect(Target, Range("A:A"))
If cell.Value = "P" Then
Application.EnableEvents = False
Sheets("Step2").Unprotect Password:="**********"
nRows = Application.InputBox("How many circuits are NOT-EXEMPT from this account?", "Insert Rows", , , , , , 1)
If nRows = 1 Then
cell.Value = "-"
Application.CutCopyMode = False
Application.EnableEvents = True
ElseIf nRows > 1 Then
cell.Value = "-"
cell.EntireRow.Copy
cell.Offset(1).Resize(nRows - 1).EntireRow.Insert xlShiftDown
Application.CutCopyMode = False
Application.EnableEvents = True
End If
End If
Next cell
Sheets("Step2").Protect Password:="********"
Sheets("Step2").EnableSelection = xlUnlockedCells
'Letscontinue:
'Application.EnableEvents = True
'Exit Sub
'Whoa:
'MsgBox Err.Description
'Resume Letscontinue
'Application.CutCopyMode = False
'Application.EnableEvents = True
End If
'Application.EnableEvents = True
End Sub
As you can see, I tried implementing my own if error code but it did not work. I still ran into the problem of excel shutting down.
Bookmarks