CODEWITHSUNDEEP
excelvba
Rob Young
Rob Young

Reputation: 15

How to prevent Worksheet_Change Event from running when adding or deleting rows?

I am a total novice when it comes to programming. I'm working on a spreadsheet to capture training information. The code listed below inputs the username (from a module the defines username as the Window username), date and time. My problem is, when rows are added or deleted, it causes the the sub to run. What can I do to prevent that? Thanks in advance for the help!

Private Sub Worksheet_Change(ByVal Target As Range)
Set Rng = Range("D11:D88")
If Not Intersect(Target, Rng) Is Nothing Then
  Application.EnableEvents = False
  Target(1).Offset(0, 1).Value = Environ$("username")
  Target(1).Offset(0, 2).Value = Date
  Target(1).Offset(0, 3).Value = Time
  Application.EnableEvents = True
End If
End Sub

Upvotes: 1

Views: 920

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Set Rng = Range("D11:D88")
    If Target.Count = 1 Then
        If Not Intersect(Target, Rng) Is Nothing Then
          Application.EnableEvents = False
          Target(1).Offset(0, 1).Value = Environ$("username")
          Target(1).Offset(0, 2).Value = Date
          Target(1).Offset(0, 3).Value = Time
          Application.EnableEvents = True
        End If
    End If
End Sub

Upvotes: 1

Related Questions