Exclude first line from macro
I have a macro that puts the current time in a cell when any row is edited. my problem is that this macro also executes for line 1, which are titles. Thus, this causes the column title to change temporarily.
The macro works fine, but it still changes the name. I tried the following:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If ActiveCell.Row = 1 Then Exit Sub
Cells(Target.Row, "I").Value = Now
Application.EnableEvents = True
End Sub
source to share
If you disable event handling, enable error management to ensure that events are re-enabled.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Safe_Exit
Application.EnableEvents = False
Dim r As Long, rw As Long, rng As Range, newTarget As Range
For Each rng In Target
If rng.Column <> 9 Then
If newTarget Is Nothing Then
Set newTarget = rng
Else
Set newTarget = Union(newTarget, rng)
End If
End If
Next rng
For r = 1 To newTarget.Rows.Count
rw = newTarget.Rows(r).Row
If rw > 1 Then _
Cells(rw, "I").Value = Now
Next r
Safe_Exit:
Application.EnableEvents = True
End Sub
If you are pasting or filling in a large number of values, Target is all the cells that have changed. You need to defend against the top row and everything else gets a time stamp. When the Target is more than one cell, you only want a timestamp for each row.
And you don't want to disable event handling and then exit without re-enabling.
source to share
The ActiveCell may change to something else after editing, so use the Target range and not ActiveCell. For example, if I press {enter} to end my edit, the ActiveCell is now on line 2, not 1.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
If .Row > 1 Then
Cells(.Row, "I").Value = Now
End If
End With
Application.EnableEvents = True
End Sub
I am using the syntax to display the same string you are comparing and the one you are editing. You can put them on separate lines if you like.
Also, Jeeped makes a good point on the Application.EnableEvents = True line. It won't work if the string is 1, so they will be disabled indefinitely. Better to test for> 1 and only run your update code in that state.
source to share