Stop Worksheet change event from interfering with macro code

Hi everyone,

I have a worksheet event that seems to be interfering with the functionality of a macro.

The macro is used for adding a specific number of new records based on the number value of a cell on the worksheet. Without the worksheet event code in place, the add record macro functions flawlessly. With it, no matter how many records are selected to be created, only one is created.

The worksheet even watches a specific cell on each row and locks and unlocks cells in the same row when the value of the watched cell changes based on 5 different possible settings. Other than it interefering with the macro functionality, it too works flawlessly otherwise.

I've been trying everything I can think of but I can't get the two to play nice together. The worksheet event code and the macro code are below. Can anyone tell me why this is happening and what I can do to fix it?

Thanks, very much!

Worksheet Event Code

Private Sub Worksheet_Change(ByVal Target As Range)
'
    If Not Intersect(Target, Range("Case_Entry")) Is Nothing Then
        Dim cell As Range
        Unprotect Password:=""
        For Each cell In Intersect(Target, Range("Case_Entry"))
            Select Case cell.Value
                Case "EOH Case"
                    cell.Offset(0, 5).Locked = False   'Column Z
                    cell.Offset(0, 6).Locked = False   'Column AA
                    cell.Offset(0, 7).Locked = True    'Column AB
                    cell.Offset(0, 8).Locked = True    'Column AC
                    cell.Offset(0, 9).Locked = True    'Column AD
                Case "EOH Enq"
                    cell.Offset(0, 5).Locked = False   'Column Z
                    cell.Offset(0, 6).Locked = False   'Column AA
                    cell.Offset(0, 7).Locked = True    'Column AB
                    cell.Offset(0, 8).Locked = True    'Column AC
                    cell.Offset(0, 9).Locked = True    'Column AD
                Case "Brochure"
                    cell.Offset(0, 5).Locked = True    'Column Z
                    cell.Offset(0, 6).Locked = True    'Column AA
                    cell.Offset(0, 7).Locked = False   'Column AB
                    cell.Offset(0, 8).Locked = False   'Column AC
                    cell.Offset(0, 9).Locked = False   'Column AD
                Case ""
                    cell.Offset(0, 5).Locked = False   'Column Z
                    cell.Offset(0, 6).Locked = False   'Column AA
                    cell.Offset(0, 7).Locked = False   'Column AB
                    cell.Offset(0, 8).Locked = False   'Column AC
                    cell.Offset(0, 9).Locked = False   'Column AD
                Case "Case"
                    cell.Offset(0, 5).Locked = True   'Column Z
                    cell.Offset(0, 6).Locked = True   'Column AA
                    cell.Offset(0, 7).Locked = True   'Column AB
                    cell.Offset(0, 8).Locked = True   'Column AC
                    cell.Offset(0, 9).Locked = True   'Column AD
                Case Else
                    cell.Offset(0, 5).Locked = False   'Column Z
                    cell.Offset(0, 6).Locked = False   'Column AA
                    cell.Offset(0, 7).Locked = False   'Column AB
                    cell.Offset(0, 8).Locked = False   'Column AC
                    cell.Offset(0, 9).Locked = False   'Column AD
                End Select
        Next cell
'    
        Protect Password:=""
    End If
'    
End Sub

Macro Code

Sub Insert_New_Case_Record_Using_Filter_Row_1()
'
' Insert_New_Case_Record_Using_Filter_Row_1
'
Dim wsh As Worksheet
Dim rng As Range
Dim i As Long
Dim iRet1 As Integer
Dim strPrompt1 As String
Dim strTitle1 As String
'
'
'
    Application.ScreenUpdating = False
'
    Set wsh = Worksheets("Cases")
     On Error Resume Next
    wsh.Unprotect Password:=""
'   
    Range("Q14:X19").Select
    Selection.ClearContents
'    
    Range("14:19").EntireRow.Hidden = True
    ActiveSheet.Range("$T$13:$Z$13").Name = "Criteria"
'
    strPrompt1 = "Please select the number of new records to be created from Q4."
    strTitle1 = "Record Creation Error"
'
    If Range("Q4") < 1 Then
    iRet1 = MsgBox(strPrompt1, vbOKOnly, strTitle1)
        If iRet1 = vbOKOnly Then
       Exit Sub
        End If
    End If
'
    If ActiveSheet.AutoFilterMode Then
        If ActiveSheet.FilterMode Then
            ActiveSheet.ShowAllData
        End If
    ElseIf ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
'
    For i = 1 To Range("Q4")
    Range("21:21").Copy
    Range("25:25").Select
    Selection.Insert Shift:=xlDown
    Range("Q13:S13").Copy
    Range("Q25:S25").PasteSpecial xlPasteValues
    Range("U13:X13").Copy
    Range("U25:X25").PasteSpecial xlPasteValues
        Next i
'    
'
    Range("DataRows").EntireRow.Hidden = False
'
    Range("24:24").EntireRow.Hidden = True
'
    Range("Q4").Value = 1
'
    wsh.Protect Password:=""
'
    Range("Q25").Select
'
    Application.ScreenUpdating = True
'
End Sub

Cytop, thanks very much! That did the trick. Still learning so didn't know about the Application.EnableEvents property. Great tip!

It works beautifully now.