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!

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


--
Gary''s Student - gsnu200902


"KSee" wrote:

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!

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


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200902


"KSee" wrote:

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!

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

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


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200902


"KSee" wrote:

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!

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


"Gary''s Student" wrote:

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

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


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200902


"KSee" wrote:

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!

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


"KSee" wrote:

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


"Gary''s Student" wrote:

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

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


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200902


"KSee" wrote:

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!

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

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

"KSee" wrote:

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


"Gary''s Student" wrote:

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

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


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200902


"KSee" wrote:

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!

--

Dave Peterson

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!
--
KSee


"Dave Peterson" wrote:

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

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

"KSee" wrote:

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


"Gary''s Student" wrote:

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

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


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200902


"KSee" wrote:

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!

--

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

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!
--
KSee

"Dave Peterson" wrote:

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

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

"KSee" wrote:

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


"Gary''s Student" wrote:

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

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


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200902


"KSee" wrote:

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!

--

Dave Peterson


--

Dave Peterson

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


"Dave Peterson" wrote:

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

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!
--
KSee

"Dave Peterson" wrote:

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

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

"KSee" wrote:

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


"Gary''s Student" wrote:

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

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


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200902


"KSee" wrote:

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!

--

Dave Peterson


--

Dave Peterson

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


"KSee" wrote:

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


"Dave Peterson" wrote:

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

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!
--
KSee

"Dave Peterson" wrote:

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

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

"KSee" wrote:

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


"Gary''s Student" wrote:

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

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


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200902


"KSee" wrote:

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!

--

Dave Peterson


--

Dave Peterson

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" wrote in message
...
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


"KSee" wrote:

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


"Dave Peterson" wrote:

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

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!
--
KSee

"Dave Peterson" wrote:

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

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

"KSee" wrote:

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


"Gary''s Student" wrote:

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

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


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200902


"KSee" wrote:

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!

--

Dave Peterson


--

Dave Peterson

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


"JP Ronse" wrote:

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" wrote in message
...
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


"KSee" wrote:

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


"Dave Peterson" wrote:

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

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!
--
KSee

"Dave Peterson" wrote:

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

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

"KSee" wrote:

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


"Gary''s Student" wrote:

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

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


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200902


"KSee" wrote:

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!

--

Dave Peterson


--

Dave Peterson

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


"KSee" wrote in message
...
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


"JP Ronse" wrote:

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" wrote in message
...
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


"KSee" wrote:

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


"Dave Peterson" wrote:

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

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!
--
KSee

"Dave Peterson" wrote:

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

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

"KSee" wrote:

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


"Gary''s Student" wrote:

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

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


"Gary''s Student" wrote:

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


--
Gary''s Student - gsnu200902


"KSee" wrote:

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!

--

Dave Peterson


--

Dave Peterson

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!
--
KSee


"JP Ronse" wrote:

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


"KSee" wrote in message
...
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


"JP Ronse" wrote:

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" wrote in message
...
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


"KSee" wrote:

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


"Dave Peterson" wrote:

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

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!
--
KSee

"Dave Peterson" wrote:

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

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

"KSee" wrote:

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


"Gary''s Student" wrote:

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

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


"Gary''s Student" wrote:

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

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


"KSee" wrote in message
...
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!
--
KSee


"JP Ronse" wrote:

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


"KSee" wrote in message
...
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


"JP Ronse" wrote:

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" wrote in message
...
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


"KSee" wrote:

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


"Dave Peterson" wrote:

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

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!
--
KSee

"Dave Peterson" wrote:

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

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

"KSee" wrote:

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


"Gary''s Student" wrote:

What is F67 merger with??
--
Gary''s Student - gsnu200902


"KSee" wrote:

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


"Gary''s Student" wrote:

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