worksheet changes seems to only work once

[theheed]

I have the following code which has been working to log row changes and also change the format of the date input. I cannot work out why, but now it only seems to produce the desired output for the first change in the worksheet then does not pick up anymore.

Any ideas??

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Dim changerow As Integer
Dim changeusername As String

changeusername = ReturnUserName()
changerow = Target.row

Range("AM" & changerow).Value = Now

Range("AN" & changerow).Value = changeusername

Application.EnableEvents = True

'when a date is entered in column V it is changed to the 1st of the month automatically

If Target.Column = 22 And IsDate(Target.Value) Then
Application.EnableEvents = False
Target.Value = DateSerial(Year(Target.Value), Month(Target.Value), 1)
Application.EnableEvents = True
End If


End Sub

 

[Gary McMaster]

Paste Application.EnableEvents = True in the immediate window and press enter.

Gary

 

[theheed]

Gary. not sure what you mean I need an Extra Enable - True?

 

[Gary McMaster]

No. What usually happens to me is the code runs into an error, or the user interrupts the code, after the Events are disabled. Once that happens the events are essentially permanently disabled because it never gets to the line that turns them back on. Since the events are disabled your event code never runs again.

You can turn them back on in the immediate window or write another utility sub to turn them back on.

What you really need to do is find out why your event handler aborted in the first place.

Gary