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...

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" wrote:

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...

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

"Mike H" wrote:

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" wrote:

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...

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.

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

"Dave" wrote:

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.

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

On Sun, 22 Jun 2008 05:05:01 -0700, Dave wrote:

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.

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.

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

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

"saran" wrote:

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