Thread: Display 'Last Modified' date in worksheet cells

Hi

I've have a workbook containing 5 worksheets and I have allocated a cell in each sheet to automatically display the date that sheet was last modified. This is to allow for each sheet to present a modified date specific to the worksheet rather than the workbook as a whole.

The code that I have implemented is displaying the "Last Modified: dd mmmm yyyy" string as I require but it is also causing Excel to crash whenever I modify/delete contents from any other cells. I'm not a coder so can anyone assist with debugging the details below please?

Version
Excel 2010 (Excel Macro-Enabled Workbook)

VB Code (against every worksheet)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim LValue As String
LValue = Format(Now(), "dd mmmm yyyy")
Range("K3") = "Last Modified: " & LValue
End Sub

Error
Microsoft Visual Basic
Run-time error ‘-2147417848 (800 10 108)’:
Method ‘_Default’ of object ‘Range’ failed

Followed by ..... Microsoft Excel has stopped working ….. Restart the program


Any help will be appreciated. Thanks.

Hello Badger72,

Welcome to the Forum!

When using the Worksheet_Change() or Worksheet_SelectionChange() events, you should make it a habit to disable the event at the beginning of the event and re-enable it before exiting the event. Otherwise the event will call itself over and over again causes a stack overflow error. Here is a basic example with error handling.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

  Dim LValue As String
    
    On Error GoTo ErrHandler

    Application.EnableEvents = False

    LValue = Format(Now(), "dd mmmm yyyy")
    Range("K3") = "Last Modified: " & LValue

ErrHandler:
    Application.EnableEvents = True

End Sub