worksheet changes seems to only work once

theheed

New Member
Joined
Aug 20, 2008
Messages
25
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,222,310
Messages
6,165,261
Members
451,949
Latest member
bovacik

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top