Update date & time in a cell only when worksheet is changed

[RJD]

I have a workbook with multiple worksheets. I'd like to have the time & date
in a cell in each worksheet update only when information in that worksheet is
changed. I don't change each worksheet each time I use the workbook. I tried
=NOW(), but it changes the time and date in each worksheet.

 

[Mike H]

Hi,

You need code for that. ALT+F11 to open VB editor. Double click
'ThisWorkbook and paste the code below in on the right. Change the cell to
the one you want

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
ActiveSheet.Range("A1").Value = Now
Application.EnableEvents = True
End Sub

Mike

 

[RJD]

Mike,

Thanks for your reply to my question. I'm new to "writing code" to
accomplish something like this Excel. I followed your instructions but may
have missed something obvious to a more experience user.

I pasted the code you provided in the window as you instructed, and changed
("A1") to ("G1") so the date & time would be in the G1 cell of my first
worksheet. I selected ALT+F11 again to go back to the worksheet and nothing
appears in G1 of worksheet 1. I'm sure your code is right and it's something
I'm doing wrong.

Since I want the date & time in each worksheet, wouldn't I select the
specific sheet instead of "ThisWorkbook" and paste the code?

I may need a little more detail in how to use VB editor this first time to
get this right.

Thanks,

RJD

 

[Gord Dibben]

RJD

After returning to Excel from VBE you must select any cell except G1 on any
sheet.

Make a change and ENTER. The date/time will appear in G1.

Having the code in Thisworkbook saves you the duplicating the code in each
worksheet.

If you just wanted it for one sheet you would change the event type to

Private Sub Worksheet_Change(ByVal Target As Range)

and paste it into the sheet module.

Mike has steered you correctly because you wanted it for every sheet in the
workbook.


Gord Dibben MS Excel MVP

 

[RJD]

Hi,
It's still not working. I pasted Mike's code in the "ThisWorkbook" VB editor
and changed ("A1") to ("H1"). I went back to the Excel file and wrote "test"
in an empty cell (not H1) of Worksheet 1 and hit "Enter." Nothing appears in
H1.

Does it matter how the cell is formatted? Right now its set for "General"
formatting.

I appreciate your help and patience.

RJD

 

[Gord Dibben]

Formatting does not matter.

Sounds like maybe you got events disabled and not re-enabled.

Mike did not add any error trapping to re-enable events in case of error,
although I cannot see much chance of an error occuring.

First go to VBE and View>Immediate Window

Copy the follwing into the window Application.EnableEvents = True

Hit enter.

Paste this amended code into Thisworkbook module........delete the original.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error GoTo endit
Application.EnableEvents = False
ActiveSheet.Range("H1").Value = Now
endit:
Application.EnableEvents = True
End Sub

Alt + q to return to a sheet. Try entering data in a cell.


Gord

 

[RJD]

I'm back with another question. Sorry it took so long to try the last
suggestion.

I followed each step below. When I pasted the information in the "Immediate
Window" screen and hit "Enter," a dialog box popped up that said "The macros
in this window are disabled. Please refer to on-line Help for..."

I did a little research on how to disable macros, but in doing so read that
there is some danger when macros are enabled. I tried some of the options
available, like keeping macros disabled and notify me when one is present.
None of those worked. I finally chose to enable macros and now the script
provided below works exactly like I requested help for (thanks, by the way).

My question is, what risk am I running having macros enabled in my copy of
Excel? I assume now any Excel file I open with a macro will open without
warning. Is there a way I can run the file I just added the script to, with
marcos disabled, "but notify?"

RD