CODEWITHSUNDEEP
vbaexcel
Kes Perron
Kes Perron

Reputation: 477

Excel VBA - Run a macro when a cell is changed

I am trying to write a macro that runs automatically any time a sheet is edited. Column H has the heading "Updated on" and the macro should put today's date in cell H# where # is the row of the cell that was changed. Here's the code I used:

Private Sub Worksheet_Change(ByVal Target As Range)

Target.Select
Range("H" & ActiveCell.Row).Select
ActiveCell.Value = Date

End Sub

After saving the workbook and changing the value of cell A2, the code put today's date into H2 as I expected, but then gave me an error. I clicked debug, and the Target.Select line was highlighted. I assumed that looping was the problem, so I updated the code:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Target.Select
Range("H" & ActiveCell.Row).Select
ActiveCell.Value = Date
Application.EnableEvents = True

End Sub

This time, I changed the value of cell B3, and it put today's date into B4. Then Excel partially froze: I could still edit that workbook, but I couldn't open or view any other workbook. I closed all the workbooks, but then Excel itself would not close and I had to use the Task Manager to end it.

Upvotes: 2

Views: 25112

Answers (2)

Tim Williams
Tim Williams

Reputation: 166126

Private Sub Worksheet_Change(ByVal Target As Range)
    const DATE_COL as long = 8
    Dim c as range

    Set c = Target.Cells(1)
    If c.Column = DATE_COL Then Exit Sub
    On Error Goto haveError
    Application.EnableEvents=False
    Me.Cells(c.Row, DATE_COL).Value = Date

haveError:
    Application.EnableEvents=True

End Sub

Upvotes: 0

Bathsheba
Bathsheba

Reputation: 234635

Using

Private Sub Worksheet_Change(ByVal Target As Range)
    Range("H" & Target.Row).Value = Date
End Sub

will give you better stability. Target is the range that's changed.

It's just possible (I'm at home so can't check) that changing the value re-fires the Worksheet_Change event. If so, then block the recursion with

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address <> Range("H" & Target.Row).Address Then        
       Range("H" & Target.Row).Value = Date
   End If
End Sub

Upvotes: 4

Related Questions