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