Application.enableEvents = false

Dim oVBComponent As Object, lCountLines As Long
        Set oVBComponent = ActiveWorkbook.VBProject.VBComponents("Sheet3")
        With oVBComponent
            lCountLines = .CodeModule.CountOfLines
            .CodeModule.DeleteLines 1, lCountLines
        End With
        Set oVBComponent = Nothing

Sub sheetsCodeModuleClear()
    Dim oVBComponent As Object, flag As Boolean, startL&, endL&
    flag = False
    'Проверяем, защищен проект или нет
    If ActiveWorkbook.VBProject.Protection = 1 Then
        MsgBox "VBProject выбранной книги защищён." & vbCrLf & _
             "     Компоненты не будут удалены.", vbExclamation, "Отмена выполнения"
        Exit Sub
    End If
    
    On Error Resume Next
    For Each oVBComponent In ActiveWorkbook.VBProject.VBComponents
        With oVBComponent
            If .Type = 100 Then
                startL = 0: endL = 0
                flag = False
                For i = 1 To .CodeModule.CountOfLines
                    If .CodeModule.Lines(i, 1) Like "*Worksheet_Change*" Then
                        startL = i
                        flag = True
                    End If
                    If flag And .CodeModule.Lines(i, 1) = "End Sub" Then
                        endL = i
                        Exit For
                    End If
                Next i
                .CodeModule.DeleteLines startL, endL - startL + 1
            End If
        End With
    Next oVBComponent
    Set oVBComponent = Nothing
End Sub

Function sheetCodeModuleClear(shName As String, eventName As String)
    Dim oVBComponent As Object, flag As Boolean, startL&, endL&
    flag = False
    'Проверяем, защищен проект или нет
    If ActiveWorkbook.VBProject.Protection = 1 Then
        MsgBox "VBProject выбранной книги защищён." & vbCrLf & _
             "     Компоненты не будут удалены.", vbExclamation, "Отмена выполнения"
        Exit Function
    End If
    
    On Error Resume Next
    For Each oVBComponent In ActiveWorkbook.VBProject.VBComponents
        With oVBComponent
            If .Type = 100 Then
                If .Name = ActiveWorkbook.Sheets(shName).CodeName Then
                    startL = 0: endL = 0
                    flag = False
                    For i = 1 To .CodeModule.CountOfLines
                        If .CodeModule.Lines(i, 1) Like "*" & eventName & "*" Then
                            startL = i
                            flag = True
                        End If
                        If flag And .CodeModule.Lines(i, 1) = "End Sub" Then
                            endL = i
                            Exit For
                        End If
                    Next i
                    .CodeModule.DeleteLines startL, endL - startL + 1
                    Exit Function
                End If
            End If
        End With
    Next oVBComponent
    Set oVBComponent = Nothing
End Function

Sub test()
sheetCodeModuleClear "Лист1", "Worksheet_Change"
End Sub

Function sheetCodeModuleClear(shName As String, eventName As String)

Sub ttt()
    Dim oVBComponent As Object, lCountLines As Long
    On Error Resume Next
    Set oVBComponent = ActiveWorkbook.VBProject.VBComponents("Sheet3")
    If Err <> 0 Then
        Set oVBComponent = ActiveWorkbook.VBProject.VBComponents("Лист3")
    End If
    On Error GoTo 0
    
    With oVBComponent
        lCountLines = .CodeModule.CountOfLines
        .CodeModule.DeleteLines 1, lCountLines
    End With
    Set oVBComponent = Nothing
End Sub

str1=worksheets(3).codename
if str1 like "Sheet*" then
     '' имя с Sheet
else
     '' имя без Sheet
end if

    On Error Resume Next
    Set oVBComponent = ActiveWorkbook.VBProject.VBComponents(shSheets)
    If Err <> 0 Then
        Exit Function
    End If
    On Error GoTo 0

Sub test()
    sheetCodeModuleClear "Лист3", "Worksheet_Change"
    sheetCodeModuleClear "Sheets3", "Worksheet_Change"
    ' для всех листов в книге
    For Each sh In ActiveWorkbook.Sheets
        sheetCodeModuleClear sh.Name, "Worksheet_Change"
    Next
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub