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!