Date of last update when sheet has changed

[KSee]

Hi there, hope somebody can help me!
I am puzzling with a problem: I have a excel 2003 wb with sheets where the
cells of 3 colums are representing the results of 1 day. In fact colums F:H
do have the results of day 1 of month January to December. Colums CC:CE the
results of day 31 of the month January to December. The cells where one can
put in or change the numeric data is the range: F2:CE65. Under each day, (3
merged colums of that day), I like to have the last update when someone has
changed the data in one of the 3 'day'colums. The problem I encounter is that
each time I reopen the workbook the 'Last update' has changed while there was
no change in the 3 colums. Also the 'Last update' in other sheets in my
workbook have changed but I want only the last updates per 'day' and sheet.
Tnx in advance!

 

[Gary''s Student]

Here is a very simple example that you can adapt.

Say we have a data entry area from G2 thru G65. If the user enters data in
this area, we want the date to be recorded in G66. Put the following event
macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Set t = Target
Set r1 = Range("G2:G65")
Set r2 = Range("G66")
If Intersect(t, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
r2.Value = Date
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[KSee]

Thank you Gary. I have the merged cell F67 formatted as "date time" but no
resulting date in this cell. Below wahta I changed in your program. Should I
fill in also 'set t = Target' ?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Set t = Target
Set r1 = Range("F3:H65")
Set r2 = Range("F67")
If Intersect(t, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
r2.Value = Date
Application.EnableEvents = True
End Sub

 

[Gary''s Student]

What is F67 merger with??

 

[KSee]

I have merged the 3 cells per day at the end of the colums to 1 cell to
create enough space for the date which should be recorded in this cell

 

[KSee]

What is wrong (see below) No date as result in "F67"

 

[Dave Peterson]

Did you put the code in the correct worksheet module?
Rightclick on the worksheet tab that should have this behavior. Choose View
Code and paste into the newly opened code window (usually on the right).

While you were testing, did you turn events off and fail to turn them on?

Open the VBE
hit ctrl-g to see the immediate window
type this
Application.EnableEvents = True
and hit enter.

Then back to excel to test.

 

[KSee]

Yes! Thank you Dave. I put in the line 'Application.EnableEvents = True' like
you proposed and changed 'r2.Value = Date' to 'r2.Value = Now' because I
wanted also the time stamp.
Is it not necessary to end with ''Application.EnableEvents = False' or
something like this. I am not familiar with programming but saw this line in
other programs.
Thanks again Gary and Dave!

 

[Dave Peterson]

Application.enableevents = false
should go before your code changes something. It tells excel to stop looking
for changes to the worksheet (actually to stop looking for lots of things). You
don't want the change your code makes to call itself.

So you have that "Application.enableevents = false" before any code that changes
something.

Then you make the change(s) and finally, you end with "Application.enableevents
= True". This "true" tells excel that you want it to go back to monitoring all
the triggers that can fire these events. So the next change the user (you???)
make to the worksheet will cause that event to fire once more.

 

[KSee]

Thanks again Dave for your help and clear explanation. Now the updated date
and time is stored and kept (also when I reopen the file) in the right cell
when a day of a month in one of the concerning colums has changed. Just like
I wanted!

 

[KSee]

Here I am back again. I was satisfied a bit to early because I can't make it
work for the whole sheet The code below is working for the first day of
the month.. What should I change to the code if I wanted to work it also for
the next 11 month and the extra colums?
So, when there is a change in colums "F3:H65", the date in cell "F67" must
be updated. When there is a change in colums "I3:K3", the date in cell "I67"
must be updated. This continues until the last range: when there is a change
in colums "CC3:CE65", the date in cell "CC67" must be updated.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r41 As Range
Set t = Target
Set r1 = Range("F3:F65")
Set r41 = Range("F67")
If Intersect(t, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Unprotect
r41.Value = Now
ActiveSheet.Protect
Application.EnableEvents = True
End Sub

Any help is very appreciated!
--
KSee

Thanks again Dave for your help and clear explanation. Now the updated date
and time is stored and kept (also when I reopen the file) in the right cell
when a day of a month in one of the concerning colums has changed. Just like
I wanted!

 

[JP Ronse]

Hi KSee,

Your code is only testing one range (F3:F65), you have to extend it for the
other ranges.

if (intersect(target, range("F3:F65") is nothing) is false then
range("F67")=now
elseif (intersect(target, range("I3:I65") is nothing) is false then
range("I67")=now
....

end if

Wkr,

JP

 

[KSee]

Hi JP, I hoped that there was a smart solution with 'Offset' or something
like that because there is a regular offset of 3 colums in the colum areas
with data as well as in the (locked) cell which kept the updated date, also a
offset of 3 colums ....
Now I get a error on 'then' in the code. I have implemented your lines but
it seems a bit of a mesh now, pls help...

Dim r1 As Range, r2 As Range, r41 As Range
Set t = Target
Set r1 = Range("F3:H65")
Set r2 = Range("I3:K65")
Set r41 = Range("F67")
Set r42 = Range("I67")
If Intersect(t, r1) Is Nothing Then Exit Sub
If (Intersect(Target, Range("F3:H65") is nothing) is false then
Range("F67") = Now
elseif (Intersect(target, range("I3:I65") is nothing) is false then
Range("I67") = Now
End If
Application.EnableEvents = False
ActiveSheet.Unprotect
r41.Value = Now
r42.Value = Now
ActiveSheet.Protect
Application.EnableEvents = True
End Sub

 

[JP Ronse]

Hi KSee,

Try following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCount As Integer

Application.EnableEvents = False
ActiveSheet.Unprotect

If (Intersect(Target, Range("F3:CE65")) Is Nothing) = False Then
intCount = Target.Column Mod 3
Cells(67, Target.Column - intCount) = Now
End If

ActiveSheet.Protect
Application.EnableEvents = True

End Sub

Wherever you change something in the range F3:CE65, the IF(INTERSECT is
true.
intcount is the reminder of the columnnumber divided by 3, e.g. changing
range("BP35") =>
intcount =68 mod 3 = > 2

Traget.column-intcount = 68-2 = column 66.

Hope this helps.

Wkr,

JP

Hi JP, I hoped that there was a smart solution with 'Offset' or something
like that because there is a regular offset of 3 colums in the colum areas
with data as well as in the (locked) cell which kept the updated date,
also a
offset of 3 colums ....
Now I get a error on 'then' in the code. I have implemented your lines but
it seems a bit of a mesh now, pls help...

Dim r1 As Range, r2 As Range, r41 As Range
Set t = Target
Set r1 = Range("F3:H65")
Set r2 = Range("I3:K65")
Set r41 = Range("F67")
Set r42 = Range("I67")
If Intersect(t, r1) Is Nothing Then Exit Sub
If (Intersect(Target, Range("F3:H65") is nothing) is false then
Range("F67") = Now
elseif (Intersect(target, range("I3:I65") is nothing) is false then
Range("I67") = Now
End If
Application.EnableEvents = False
ActiveSheet.Unprotect
r41.Value = Now
r42.Value = Now
ActiveSheet.Protect
Application.EnableEvents = True
End Sub

 

[KSee]

Hi JP,
This is fabulous, fantastic! With only 10 lines of code. It is working for
the whole area exactly as I hoped. I am still studying how exactly you did it
(using Mod 3), but with your explanation I will understand it In Dutch we
say: "Petje af" ("Cap off") or something like that
Thanks a lot and have a nice weekend, for me it is already now!

 

[JP Ronse]

Hi KSee,

You're welcome and glad I could help.

You brought me to the idea to use mod 3. In a previous mail you write that
there is a fix offset of 3.

Your range starts at column "F", which is column 6. The MOD function returns
the remainder of the devision, so
6 MOD 3 returns 0 because there is no remainder.
For Column G (7), MOD will return 1, H (8) will return 2, I (9) will return
0 and so on.

You want the timestamp in F, I, L ...

If we change e.g. column H, then cells(67, "H"-2) points to cells(67,8-2=6).

Thanks for the compliment, I'm Dutch speaking too.

Wkr,

JP