Method 'Range' of object'_Worksheet' failed

Question

Hi,

I have an Excel workbook with a couple of tabs.  What I want is that any time any cell is edited in the workbook, the cell "F2" in the tab "Safety Calculation" updates to today's date.

This is the code I've created:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Worksheets("Safety Calculation").Range("F2") = Date

End Sub

I keep getting the Method 'Range' of object'_Worksheet' failed error.  What's wrong with my code?

Thanks,

GSalvat

Answer 1

Hi,

Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    On Error GoTo ExitPoint

Application.EnableEvents = False

    Worksheets("Safety Calculation").Range("F2") = Date

ExitPoint:

Application.EnableEvents = True

End Sub

You need to disable the Events, otherwise the act of placing the date in F2 re-triggers the event infinitely.  Use the error handling so that events are always left enabled.

Cheers

Rich

Answer 2

Seems to be working, thanks!

You're welcome.  Please mark my previous post as the answer.

Cheers

Rich

Answer 3

Seems to be working, thanks!