date last updated

[Rusty]

I looking for a formula that will display the date in a cell (A2), if data
in a range of cells (B5:K17) has been changed. I'm very new to EXCEL and
am trying to teach myself. Im sure it can be done with a macro but I havent
started with macro,s yet. =A2,TODAY()IFB2:K17

 

[Norman Harker]

Hi Rusty!

You'll need a Worksheet_Change VBA subroutine for this type of
facility.

Try the following that seems to work OK:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If Not Intersect(.Cells, Range("B5:K17")) Is Nothing Then
Application.EnableEvents = False
With Worksheets("Sheet11").Range("A1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub

This goes in the Sheet Module for the sheet with your range B5:K17.
You'll need to amend "Sheet11" appropriately and you might want your
datestamp in a cell other than A1.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[JMay]

Norman:
This is great code. But how can you make this a bit more Fool-proof?
For example, I notice that if within the intersect range in an already blank
cell (null) if you simply
press the delete key on, the Worksheets("Sheet11").Range("A1") updates...???
meaning a change was registered, when in fact "nothing changed, cause
nothing was there";
don't mean to be a pest but was just wondering.
Tks,
JMay

 

[Norman Harker]

Hi JMay!

Not easy without recording and keeping a running record of all the
data in that block of cells.

If all of the data was numeric, you could just keep track of the sum
of the range and not datestamp if it is unchanged. That also covers a
simple entry that doesn't change the cell but won't cover a formula
entry that returns the same value.

My inclination is to use a Workbook_BeforeSave approach anyway as
these Worksheet_Change event handlers can suck up a lot of processing.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Rusty]

Couldn't you keep a hidden copy of the page and compare on close? (is that
what you ment?)

 

[Rusty]

I tried this on a test (blank work sheet) and it worked fine. when I put it
to my db it wont do anything. I tried to change the format and tried to
debug.
could I have the settings wrong?

 

[Norman Harker]

Hi Rusty!

Could be all sorts of things but not doing anything indicates a
problem with the Range or Sheet name so that you're not getting an
intersect.

Did you put the subroutine in the sheet module for the workbook?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Rusty!

That's one potential approach but with a Worksheet_Change event
handler you really start sucking up power because you are doing a lot
of processing every time you enter something.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.