Run-Time Error 1004 when displaying Date/Time

Question

The code below is used to recognise when a change occurs on any sheet in the workbook. Then on the sheet where the changes happened, show the date + time in cell B1 and the user who made the change in D1.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    With Sh
        If Intersect(Target, Range("A1:D1")) Is Nothing Then
            ActiveSheet.Range("B1").Value = Format(Now(), "dd/mm/yyyy - hh:mm:ss")
            ActiveSheet.Range("D1").Value = Application.UserName
        Else
        End If
    End With
End Sub

But the code errors when trying to action somehing like copy+paste cells in one sheet or across 2

Tried looking for solutions and using different syntax (like wsPOD instead of Activesheet) but either errors with same actions or errors on any change.

When you copy from one sheet and try to paste to an array of sheets then the `Target` and the `Range("A1:D1")` refer to different sheets. You can check that by putting a break point and then checking `?Target.Parent.Name` and similarly `?Range("A1:D1").Parent.Name` and you will see that they are different and hence the error. You can get rid of that by fully qualifying the range object. For example, `If Intersect(Target, Sh.Range("A1:D1")) Is Nothing Then`. You will not get an error now. — Siddharth Rout, Mar 14 '23 at 08:14
Similarly replace `ActiveSheet` with `Sh` in the other two lines. Or rather simply delete `ActiveSheet` and let `.Range("B1").Value` remain so that `With sh` qualifies it. — Siddharth Rout, Mar 14 '23 at 08:18

VBasic2008 · Answer 1 · 2023-03-14T09:03:09.530

A Workbook Sheet Change: Time Stamp And User

If you don't disable events, then the event will trigger each time you write to the cell again and again resulting in an endless loop, probably crashing Excel.
Also, you should have used:
```
If Not Intersect(Target, .Range("A1:D1")) Is Nothing Then
```
focus on Not, and the dot in front of Range i.e. .Range. The following code uses the opposite to exit the procedure.

The Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    ' Restrict to a range e.g. 'A1:D1'
    If Intersect(Target, Sh.Range("A1:D1")) Is Nothing Then Exit Sub
    ' Out-comment the above line to use for a change in any cell.
    
    Application.EnableEvents = False ' to not retrigger the event
        Sh.Range("B1").Value = Format(Now, "dd/mm/yyyy - hh:mm:ss")
        Sh.Range("D1").Value = Application.UserName
    Application.EnableEvents = True

End Sub

nicely done. Also since you are working with `EnableEvents `, you may want to introduce proper error handling? I have spoken about it [HERE](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure) — Siddharth Rout, Mar 14 '23 at 08:54

Run-Time Error 1004 when displaying Date/Time

1 Answers1

A Workbook Sheet Change: Time Stamp And User