Date does not update until worksheet change

[spreadsheetlady]

Hi,
My work sheet has a date in cell: "A1".

I would like the date to update (only) if there is a change in the sheet.

I'm thinking of this to start:
Something like Private Sub Workbook_SheetChange(By Val Sh As Object, By Val_
Target As Excel.Range)

Thanks in advance,
Amy

 

[Ryan H]

The SheetChange Event fires when you activate a different sheet within the
workbook. I think this is what you are wanting.

Put this code in worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A1").Value = Date
End Sub

 

[spreadsheetlady]

Thank-you for answering Ryan.

The procedure you wrote works the way I wanted it to, but....

I get a run error at the end of the procedure: " Method 'Value' of Object
'Range' failed."

It hilites this line of code in yellow: Range("A1").Value = Date

I don't understand why it's doing it. It seems to do what we want it to.
But when it's done running, it shows the above run error.

I have Excel 2007.

Amy

 

[Ryan H]

Oops! You are right. Use this code instead.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1").Value = Date
Application.EnableEvents = True
End Sub

The problem is when you change the value of Range A1 the Worksheet Change
Event fires again, and again, and again and again, resulting in an endless
loop which throws an error. This code will temporarly disable the event
while the data is changed.

Hope this helps! If so, click "YES" below.

 

[spreadsheetlady]

Oops! You are right. Use this code instead.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("A1").Value = Date
Application.EnableEvents = True
End Sub

The problem is when you change the value of Range A1 the Worksheet Change
Event fires again, and again, and again and again, resulting in an endless
loop which throws an error. This code will temporarly disable the event
while the data is changed.

Hope this helps! If so, click "YES" below.

 

[spreadsheetlady]

Thanks Ryan. That fixed it. The routine runs great.
Amy