VBA-Authored Formula Error

[Aimee S.]

Using this code in VBA for an .xlsm workbook for a =LastChange formula that gives the last date and time any modification to the workbook was made:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ThisWorkbook.Names("LastChange").RefersTo = Now
End Sub

I added the code to ThisWorkbook View Code, then type =LastChange in a cell and get #NAME?.

I have to be missing a step here. Any help?

 

[mikerickson]

I think what you want is either

ThisWorkbook.Names.Add Name:="LastChange", RefersTo:= "=NOW()"

or

ThisWorkbook.Names.Add Name:="LastChange", RefersTo:= "=" & Now

 

[Aimee S.]

Where do I enter either of those two options?

 

[jmthompson]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ThisWorkbook.Names.Add Name:="LastChange", RefersToR1C1:="=NOW()"
End Sub

 

[Aimee S.]

I overwrote the old code with this one. I think they both work the same. The stupid mistake I made was not selecting and naming the range to be monitored "LastChange" It is working now hehe