Worksheet Event Code

[ayazgreat]

Hi

I have following codes, there is a problem in these codes, when I write mistakenly any word or number instead of D or d in target column then macro stops to work even I write again D or d then macro does not work and I have close workbook and reopen it and write correct word to get the macro worked. Could anybody give me some help to correct it ?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
      
    If Target.Column = 11 Then
        Application.EnableEvents = False
        If Target.Value = "D" Or Target.Value = "d" Then
            Target.EntireRow.Copy
            Worksheets("Issued Record").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
            Target.EntireRow.Delete
            Application.EnableEvents = True
             
            Exit Sub
        End If
    End If
End Sub

 

[VoG]

Just change where you disable events

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 11 Then
        If Target.Value = "D" Or Target.Value = "d" Then
            Application.EnableEvents = False
            Target.EntireRow.Copy
            Worksheets("Issued Record").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
            Target.EntireRow.Delete
            Application.EnableEvents = True
            Exit Sub
        End If
    End If
End Sub

 

[ayazgreat]

Sorry I do not understand what you mean

 

[VoG]

Just change it to the code that I posted.

 

[ayazgreat]

OK Peter , Thanks for you help

 

[Rick Rothstein]

Sorry I do not understand what you mean

Look at this part of your code...

    If Target.Column = 11 Then
        [COLOR=red]Application.EnableEvents = False[/COLOR]
        If Target.Value = "D" Or Target.Value = "d" Then
            Target.EntireRow.Copy
            Worksheets("Issued Record").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
            Target.EntireRow.Delete
            [COLOR=red]Application.EnableEvents = True[/COLOR]
            Exit Sub
        End If

The Application.EnableEvents=False statement disables events BEFORE you test for the Target.Value equalling "D" or "d"; HOWEVER, you only execute the Application.EnableEvents=True statement IF Target.Value equals "D" or "d'. So, if it doesn't equal "D" or "d", the events remain turned off.