auto update of date and time whenever changes are made to a xcel s

[saran]

How to set auto update of date and time in a excel sheet, so that whenever
any changes to the sheet (any information is changed) is made and saved, it
automatically updates the date and time of the change...

 

[Mike H]

Hi,

One way. Alt +F11 to open vb editor. Double click 'This workbook' and paste
this in on the right.

Change the sheet and range to suit your requirements.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("A1").Value = "Last saved " & Now
End Sub

Mike

 

[saran]

Hi..Thanks for the response. Can u please elaborate on the following.

1) My excel sheet doesn't respond to Alt +F11. Is there a specific location
in the excel sheet, wherin which this shortcut key works?

2) Where do i copy & paste this info u had sent?

Saran

 

[Dave]

Hi,
If Alt with F11 isn't working, right-click your sheet tab and choose View
Code.
The VBA window will open.
Paste Mike's code into the blank section on the right, then close the VBA
window.
Dave.

 

[Mike H]

Hi,

It has to be pasted into 'This Workbook' so as Dave says, right click the
sheet tab, view code, double click 'This workbook' and then paste it in on
the right.

Mike

 

[Gord Dibben]

Dave

I think you meant to say "right-click on the Excel Icon left of "File" on the
menu"

The code Mike posted is workbook event code, not sheet event code.


Gord Dibben MS Excel MVP

 

[saran]

Hi Dave.
Thanks for the response.
But view code option is not highlighted in the work sheet and hence could
not select it.Can u pl let me know what may b the problem.

 

[saran]

Hi...
I could resolve the problem of my previous post.

The solution given by u for the autoupdate is working satisfactorily.

Thank u very much for the support

Saran

 

[saran]

Hi..
this is working fine if i start a new work sheet. But when i try to
incorporate it in an already available worksheet, it says run time error 9.

Can u pl suggest what may b the problem.

 

[saran]

This problem is more predominant in an excel sheet having more than one sheet.
Can u pl help me to fix the problem