#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Auto Stamp Date

I am creating a spreadsheet and would like to know if I am working in row 1
and I make add, update or change informaiton in cells B1, C1 or D1 is it
possible to auto stamp the time and date of the add, update or change in cell
E1? If so can you help me understand how to write the formula?

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 492
Default Auto Stamp Date

You can't do it with a formula because the NOW() function will update every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is Nothing Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in brackets
Expand that and double click Sheet1 or whatever sheet you want to do this on
Copy and paste the code into this new window, (watch or text wrap in the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in message
...
I am creating a spreadsheet and would like to know if I am working in row 1
and I make add, update or change informaiton in cells B1, C1 or D1 is it
possible to auto stamp the time and date of the add, update or change in
cell
E1? If so can you help me understand how to write the formula?


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Auto Stamp Date

Alan, thanks so much for the help. I have found the Alt F11 but where do I
copy and paste the below formula once the VBA Project window is open?

"Alan" wrote:

You can't do it with a formula because the NOW() function will update every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is Nothing Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in brackets
Expand that and double click Sheet1 or whatever sheet you want to do this on
Copy and paste the code into this new window, (watch or text wrap in the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in message
...
I am creating a spreadsheet and would like to know if I am working in row 1
and I make add, update or change informaiton in cells B1, C1 or D1 is it
possible to auto stamp the time and date of the add, update or change in
cell
E1? If so can you help me understand how to write the formula?



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 492
Default Auto Stamp Date

Click the 'VBProject' icon on the top left,
You should see 'Microsoft Excel Objects'
Under that are three icons, Sheet1, Sheet2 and Sheet3
Double click the sheet you want this in and copy and paste the code into the
new blank window in the middle,
Post back and tell us how you got on!
Alan.

"ShePink77" wrote in message
...
Alan, thanks so much for the help. I have found the Alt F11 but where do
I
copy and paste the below formula once the VBA Project window is open?

"Alan" wrote:

You can't do it with a formula because the NOW() function will update
every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is Nothing Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in brackets
Expand that and double click Sheet1 or whatever sheet you want to do this
on
Copy and paste the code into this new window, (watch or text wrap in the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in message
...
I am creating a spreadsheet and would like to know if I am working in
row 1
and I make add, update or change informaiton in cells B1, C1 or D1 is
it
possible to auto stamp the time and date of the add, update or change
in
cell
E1? If so can you help me understand how to write the formula?




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Auto Stamp Date

Easiest method to add event code to a sheet is right-click on the sheet tab
and select "View Code"

Copy/paste the code into that module.

Edit if desired then Alt + q to go back to the Excel window.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 12:49:07 -0700, ShePink77
wrote:

Alan, thanks so much for the help. I have found the Alt F11 but where do I
copy and paste the below formula once the VBA Project window is open?

"Alan" wrote:

You can't do it with a formula because the NOW() function will update every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is Nothing Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in brackets
Expand that and double click Sheet1 or whatever sheet you want to do this on
Copy and paste the code into this new window, (watch or text wrap in the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in message
...
I am creating a spreadsheet and would like to know if I am working in row 1
and I make add, update or change informaiton in cells B1, C1 or D1 is it
possible to auto stamp the time and date of the add, update or change in
cell
E1? If so can you help me understand how to write the formula?






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Auto Stamp Date

It is funny that I found a post of the exact thing I need...posted on the
exact same day. Alan and Gord - I have followed your direction and posted
the code in VB but, nothing is appearing when I make a change to cells A2 to
C2 (my range is a little different). I have edited the code to suit my
needs. What am I doing wrong? My data is in cells A2:C2 and I want the
recorded change to post in D2.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A2:C2")) Is Nothing Then
Range("D2") = Now
Application.EnableEvents = True
End If
End Sub

"Gord Dibben" wrote:

Easiest method to add event code to a sheet is right-click on the sheet tab
and select "View Code"

Copy/paste the code into that module.

Edit if desired then Alt + q to go back to the Excel window.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 12:49:07 -0700, ShePink77
wrote:

Alan, thanks so much for the help. I have found the Alt F11 but where do I
copy and paste the below formula once the VBA Project window is open?

"Alan" wrote:

You can't do it with a formula because the NOW() function will update every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is Nothing Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in brackets
Expand that and double click Sheet1 or whatever sheet you want to do this on
Copy and paste the code into this new window, (watch or text wrap in the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in message
...
I am creating a spreadsheet and would like to know if I am working in row 1
and I make add, update or change informaiton in cells B1, C1 or D1 is it
possible to auto stamp the time and date of the add, update or change in
cell
E1? If so can you help me understand how to write the formula?





  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 492
Default Auto Stamp Date

Hard to say. This an event code and it will only work if the cells are
physically changed, ie the cell is altered directly. It won't work by the
result of a formula in the cell. If you want that to happen you need to
include the cells that the formula refers to.
Did you get at any stage an error message that opened the VB debugger? If
you did, resolve it, save the file and reopen it,
Regards,
Alan.
"DataGuy" wrote in message
...
It is funny that I found a post of the exact thing I need...posted on the
exact same day. Alan and Gord - I have followed your direction and posted
the code in VB but, nothing is appearing when I make a change to cells A2
to
C2 (my range is a little different). I have edited the code to suit my
needs. What am I doing wrong? My data is in cells A2:C2 and I want the
recorded change to post in D2.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A2:C2")) Is Nothing Then
Range("D2") = Now
Application.EnableEvents = True
End If
End Sub

"Gord Dibben" wrote:

Easiest method to add event code to a sheet is right-click on the sheet
tab
and select "View Code"

Copy/paste the code into that module.

Edit if desired then Alt + q to go back to the Excel window.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 12:49:07 -0700, ShePink77
wrote:

Alan, thanks so much for the help. I have found the Alt F11 but where
do I
copy and paste the below formula once the VBA Project window is open?

"Alan" wrote:

You can't do it with a formula because the NOW() function will update
every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is Nothing Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in brackets
Expand that and double click Sheet1 or whatever sheet you want to do
this on
Copy and paste the code into this new window, (watch or text wrap in
the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in message
...
I am creating a spreadsheet and would like to know if I am working in
row 1
and I make add, update or change informaiton in cells B1, C1 or D1
is it
possible to auto stamp the time and date of the add, update or
change in
cell
E1? If so can you help me understand how to write the formula?






  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Auto Stamp Date

Alan, I did get the formula to work the trick was to save my spreadsheet and
then reopen. One last question how do I copy and paste this formula to all
of column E? For example: If I make any update in a specific row only the
date will auto stamp in column E of that same row.

"Alan" wrote:

Hard to say. This an event code and it will only work if the cells are
physically changed, ie the cell is altered directly. It won't work by the
result of a formula in the cell. If you want that to happen you need to
include the cells that the formula refers to.
Did you get at any stage an error message that opened the VB debugger? If
you did, resolve it, save the file and reopen it,
Regards,
Alan.
"DataGuy" wrote in message
...
It is funny that I found a post of the exact thing I need...posted on the
exact same day. Alan and Gord - I have followed your direction and posted
the code in VB but, nothing is appearing when I make a change to cells A2
to
C2 (my range is a little different). I have edited the code to suit my
needs. What am I doing wrong? My data is in cells A2:C2 and I want the
recorded change to post in D2.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A2:C2")) Is Nothing Then
Range("D2") = Now
Application.EnableEvents = True
End If
End Sub

"Gord Dibben" wrote:

Easiest method to add event code to a sheet is right-click on the sheet
tab
and select "View Code"

Copy/paste the code into that module.

Edit if desired then Alt + q to go back to the Excel window.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 12:49:07 -0700, ShePink77
wrote:

Alan, thanks so much for the help. I have found the Alt F11 but where
do I
copy and paste the below formula once the VBA Project window is open?

"Alan" wrote:

You can't do it with a formula because the NOW() function will update
every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is Nothing Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in brackets
Expand that and double click Sheet1 or whatever sheet you want to do
this on
Copy and paste the code into this new window, (watch or text wrap in
the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in message
...
I am creating a spreadsheet and would like to know if I am working in
row 1
and I make add, update or change informaiton in cells B1, C1 or D1
is it
possible to auto stamp the time and date of the add, update or
change in
cell
E1? If so can you help me understand how to write the formula?







  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 20
Default Auto Stamp Date

Folks, I had the exact same question yesterday. I tried the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("V10:V499")) Is Number Then
Range("U10:U499") = Now
End If
End Sub

Something seems to be wrong, though. Can anyone troubleshoot this for me?

"ShePink77" wrote:

Alan, I did get the formula to work the trick was to save my spreadsheet and
then reopen. One last question how do I copy and paste this formula to all
of column E? For example: If I make any update in a specific row only the
date will auto stamp in column E of that same row.

"Alan" wrote:

Hard to say. This an event code and it will only work if the cells are
physically changed, ie the cell is altered directly. It won't work by the
result of a formula in the cell. If you want that to happen you need to
include the cells that the formula refers to.
Did you get at any stage an error message that opened the VB debugger? If
you did, resolve it, save the file and reopen it,
Regards,
Alan.
"DataGuy" wrote in message
...
It is funny that I found a post of the exact thing I need...posted on the
exact same day. Alan and Gord - I have followed your direction and posted
the code in VB but, nothing is appearing when I make a change to cells A2
to
C2 (my range is a little different). I have edited the code to suit my
needs. What am I doing wrong? My data is in cells A2:C2 and I want the
recorded change to post in D2.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A2:C2")) Is Nothing Then
Range("D2") = Now
Application.EnableEvents = True
End If
End Sub

"Gord Dibben" wrote:

Easiest method to add event code to a sheet is right-click on the sheet
tab
and select "View Code"

Copy/paste the code into that module.

Edit if desired then Alt + q to go back to the Excel window.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 12:49:07 -0700, ShePink77
wrote:

Alan, thanks so much for the help. I have found the Alt F11 but where
do I
copy and paste the below formula once the VBA Project window is open?

"Alan" wrote:

You can't do it with a formula because the NOW() function will update
every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is Nothing Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in brackets
Expand that and double click Sheet1 or whatever sheet you want to do
this on
Copy and paste the code into this new window, (watch or text wrap in
the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in message
...
I am creating a spreadsheet and would like to know if I am working in
row 1
and I make add, update or change informaiton in cells B1, C1 or D1
is it
possible to auto stamp the time and date of the add, update or
change in
cell
E1? If so can you help me understand how to write the formula?







  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 492
Default Auto Stamp Date

Try this,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("V10:V499")) Is Nothing Then
Range("U10:U499") = Now
Application.EnableEvents = True
End If
End Sub

You have

If Not Application.Intersect(Target, Range("V10:V499")) Is Number Then

It should be

If Not Application.Intersect(Target, Range("V10:V499")) Is Nothing Then

Regards,
Alan.

"EMW103" wrote in message
...
Folks, I had the exact same question yesterday. I tried the following
code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("V10:V499")) Is Number Then
Range("U10:U499") = Now
End If
End Sub

Something seems to be wrong, though. Can anyone troubleshoot this for me?

"ShePink77" wrote:

Alan, I did get the formula to work the trick was to save my spreadsheet
and
then reopen. One last question how do I copy and paste this formula to
all
of column E? For example: If I make any update in a specific row only
the
date will auto stamp in column E of that same row.

"Alan" wrote:

Hard to say. This an event code and it will only work if the cells are
physically changed, ie the cell is altered directly. It won't work by
the
result of a formula in the cell. If you want that to happen you need to
include the cells that the formula refers to.
Did you get at any stage an error message that opened the VB debugger?
If
you did, resolve it, save the file and reopen it,
Regards,
Alan.
"DataGuy" wrote in message
...
It is funny that I found a post of the exact thing I need...posted on
the
exact same day. Alan and Gord - I have followed your direction and
posted
the code in VB but, nothing is appearing when I make a change to
cells A2
to
C2 (my range is a little different). I have edited the code to suit
my
needs. What am I doing wrong? My data is in cells A2:C2 and I want
the
recorded change to post in D2.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A2:C2")) Is Nothing Then
Range("D2") = Now
Application.EnableEvents = True
End If
End Sub

"Gord Dibben" wrote:

Easiest method to add event code to a sheet is right-click on the
sheet
tab
and select "View Code"

Copy/paste the code into that module.

Edit if desired then Alt + q to go back to the Excel window.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 12:49:07 -0700, ShePink77
wrote:

Alan, thanks so much for the help. I have found the Alt F11 but
where
do I
copy and paste the below formula once the VBA Project window is
open?

"Alan" wrote:

You can't do it with a formula because the NOW() function will
update
every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is Nothing
Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in
brackets
Expand that and double click Sheet1 or whatever sheet you want to
do
this on
Copy and paste the code into this new window, (watch or text wrap
in
the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in
message
...
I am creating a spreadsheet and would like to know if I am
working in
row 1
and I make add, update or change informaiton in cells B1, C1 or
D1
is it
possible to auto stamp the time and date of the add, update or
change in
cell
E1? If so can you help me understand how to write the formula?










  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 20
Default Auto Stamp Date

No luck, Alan. Here's the situation: if a cell in column V has a currency
amount in it (which is the result of a formula involving other columns), I
want a date stamp in the corresponding cell in column U. Which is why I
wonder if the programming below, which indicates that column V should have
"Nothing" is correct? My original formula for column U, for reference, was
=IF(ISNUMBER(V10), NOW(), ""), but NOW produces a constantly updated date
every time you open the document, which I don't want.

"Alan" wrote:

Try this,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("V10:V499")) Is Nothing Then
Range("U10:U499") = Now
Application.EnableEvents = True
End If
End Sub

You have

If Not Application.Intersect(Target, Range("V10:V499")) Is Number Then

It should be

If Not Application.Intersect(Target, Range("V10:V499")) Is Nothing Then

Regards,
Alan.

"EMW103" wrote in message
...
Folks, I had the exact same question yesterday. I tried the following
code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("V10:V499")) Is Number Then
Range("U10:U499") = Now
End If
End Sub

Something seems to be wrong, though. Can anyone troubleshoot this for me?

"ShePink77" wrote:

Alan, I did get the formula to work the trick was to save my spreadsheet
and
then reopen. One last question how do I copy and paste this formula to
all
of column E? For example: If I make any update in a specific row only
the
date will auto stamp in column E of that same row.

"Alan" wrote:

Hard to say. This an event code and it will only work if the cells are
physically changed, ie the cell is altered directly. It won't work by
the
result of a formula in the cell. If you want that to happen you need to
include the cells that the formula refers to.
Did you get at any stage an error message that opened the VB debugger?
If
you did, resolve it, save the file and reopen it,
Regards,
Alan.
"DataGuy" wrote in message
...
It is funny that I found a post of the exact thing I need...posted on
the
exact same day. Alan and Gord - I have followed your direction and
posted
the code in VB but, nothing is appearing when I make a change to
cells A2
to
C2 (my range is a little different). I have edited the code to suit
my
needs. What am I doing wrong? My data is in cells A2:C2 and I want
the
recorded change to post in D2.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A2:C2")) Is Nothing Then
Range("D2") = Now
Application.EnableEvents = True
End If
End Sub

"Gord Dibben" wrote:

Easiest method to add event code to a sheet is right-click on the
sheet
tab
and select "View Code"

Copy/paste the code into that module.

Edit if desired then Alt + q to go back to the Excel window.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 12:49:07 -0700, ShePink77
wrote:

Alan, thanks so much for the help. I have found the Alt F11 but
where
do I
copy and paste the below formula once the VBA Project window is
open?

"Alan" wrote:

You can't do it with a formula because the NOW() function will
update
every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is Nothing
Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in
brackets
Expand that and double click Sheet1 or whatever sheet you want to
do
this on
Copy and paste the code into this new window, (watch or text wrap
in
the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in
message
...
I am creating a spreadsheet and would like to know if I am
working in
row 1
and I make add, update or change informaiton in cells B1, C1 or
D1
is it
possible to auto stamp the time and date of the add, update or
change in
cell
E1? If so can you help me understand how to write the formula?









  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 492
Default Auto Stamp Date

It's not column V that is Nothing, it's Application.Intersect(Taget......
that's Nothing.
As I understand it, you want an individual date stamp as the result of a
formula in the adjacent cell. This code won't do that, it will only work if
the cell has been changed by the keyboard, a change made by a formula by
entering something into another cell won't trigger it.
I don't know how to do that I'm afraid, you should ask in Excel Programming,
Good luck,
Alan.
"EMW103" wrote in message
...
No luck, Alan. Here's the situation: if a cell in column V has a currency
amount in it (which is the result of a formula involving other columns), I
want a date stamp in the corresponding cell in column U. Which is why I
wonder if the programming below, which indicates that column V should have
"Nothing" is correct? My original formula for column U, for reference,
was
=IF(ISNUMBER(V10), NOW(), ""), but NOW produces a constantly updated date
every time you open the document, which I don't want.

"Alan" wrote:

Try this,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("V10:V499")) Is Nothing Then
Range("U10:U499") = Now
Application.EnableEvents = True
End If
End Sub

You have

If Not Application.Intersect(Target, Range("V10:V499")) Is Number Then

It should be

If Not Application.Intersect(Target, Range("V10:V499")) Is Nothing Then

Regards,
Alan.

"EMW103" wrote in message
...
Folks, I had the exact same question yesterday. I tried the following
code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("V10:V499")) Is Number Then
Range("U10:U499") = Now
End If
End Sub

Something seems to be wrong, though. Can anyone troubleshoot this for
me?

"ShePink77" wrote:

Alan, I did get the formula to work the trick was to save my
spreadsheet
and
then reopen. One last question how do I copy and paste this formula
to
all
of column E? For example: If I make any update in a specific row
only
the
date will auto stamp in column E of that same row.

"Alan" wrote:

Hard to say. This an event code and it will only work if the cells
are
physically changed, ie the cell is altered directly. It won't work
by
the
result of a formula in the cell. If you want that to happen you need
to
include the cells that the formula refers to.
Did you get at any stage an error message that opened the VB
debugger?
If
you did, resolve it, save the file and reopen it,
Regards,
Alan.
"DataGuy" wrote in message
...
It is funny that I found a post of the exact thing I need...posted
on
the
exact same day. Alan and Gord - I have followed your direction
and
posted
the code in VB but, nothing is appearing when I make a change to
cells A2
to
C2 (my range is a little different). I have edited the code to
suit
my
needs. What am I doing wrong? My data is in cells A2:C2 and I
want
the
recorded change to post in D2.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A2:C2")) Is Nothing
Then
Range("D2") = Now
Application.EnableEvents = True
End If
End Sub

"Gord Dibben" wrote:

Easiest method to add event code to a sheet is right-click on the
sheet
tab
and select "View Code"

Copy/paste the code into that module.

Edit if desired then Alt + q to go back to the Excel window.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 12:49:07 -0700, ShePink77
wrote:

Alan, thanks so much for the help. I have found the Alt F11 but
where
do I
copy and paste the below formula once the VBA Project window is
open?

"Alan" wrote:

You can't do it with a formula because the NOW() function will
update
every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is
Nothing
Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in
brackets
Expand that and double click Sheet1 or whatever sheet you want
to
do
this on
Copy and paste the code into this new window, (watch or text
wrap
in
the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in
message
...
I am creating a spreadsheet and would like to know if I am
working in
row 1
and I make add, update or change informaiton in cells B1, C1
or
D1
is it
possible to auto stamp the time and date of the add, update
or
change in
cell
E1? If so can you help me understand how to write the
formula?










  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Auto Stamp Date

If your changes in V10:V499 are the results of formulas you must use the
Worksheet_Calculate event, not the Sheet_Change event.

Private Sub Worksheet_Calculate()
the code
End Sub


Gord Dibben MS Excel MVP

On Fri, 27 Mar 2009 10:00:01 -0700, EMW103
wrote:

No luck, Alan. Here's the situation: if a cell in column V has a currency
amount in it (which is the result of a formula involving other columns), I
want a date stamp in the corresponding cell in column U. Which is why I
wonder if the programming below, which indicates that column V should have
"Nothing" is correct? My original formula for column U, for reference, was
=IF(ISNUMBER(V10), NOW(), ""), but NOW produces a constantly updated date
every time you open the document, which I don't want.

"Alan" wrote:

Try this,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("V10:V499")) Is Nothing Then
Range("U10:U499") = Now
Application.EnableEvents = True
End If
End Sub

You have

If Not Application.Intersect(Target, Range("V10:V499")) Is Number Then

It should be

If Not Application.Intersect(Target, Range("V10:V499")) Is Nothing Then

Regards,
Alan.

"EMW103" wrote in message
...
Folks, I had the exact same question yesterday. I tried the following
code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("V10:V499")) Is Number Then
Range("U10:U499") = Now
End If
End Sub

Something seems to be wrong, though. Can anyone troubleshoot this for me?

"ShePink77" wrote:

Alan, I did get the formula to work the trick was to save my spreadsheet
and
then reopen. One last question how do I copy and paste this formula to
all
of column E? For example: If I make any update in a specific row only
the
date will auto stamp in column E of that same row.

"Alan" wrote:

Hard to say. This an event code and it will only work if the cells are
physically changed, ie the cell is altered directly. It won't work by
the
result of a formula in the cell. If you want that to happen you need to
include the cells that the formula refers to.
Did you get at any stage an error message that opened the VB debugger?
If
you did, resolve it, save the file and reopen it,
Regards,
Alan.
"DataGuy" wrote in message
...
It is funny that I found a post of the exact thing I need...posted on
the
exact same day. Alan and Gord - I have followed your direction and
posted
the code in VB but, nothing is appearing when I make a change to
cells A2
to
C2 (my range is a little different). I have edited the code to suit
my
needs. What am I doing wrong? My data is in cells A2:C2 and I want
the
recorded change to post in D2.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A2:C2")) Is Nothing Then
Range("D2") = Now
Application.EnableEvents = True
End If
End Sub

"Gord Dibben" wrote:

Easiest method to add event code to a sheet is right-click on the
sheet
tab
and select "View Code"

Copy/paste the code into that module.

Edit if desired then Alt + q to go back to the Excel window.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 12:49:07 -0700, ShePink77
wrote:

Alan, thanks so much for the help. I have found the Alt F11 but
where
do I
copy and paste the below formula once the VBA Project window is
open?

"Alan" wrote:

You can't do it with a formula because the NOW() function will
update
every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is Nothing
Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in
brackets
Expand that and double click Sheet1 or whatever sheet you want to
do
this on
Copy and paste the code into this new window, (watch or text wrap
in
the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in
message
...
I am creating a spreadsheet and would like to know if I am
working in
row 1
and I make add, update or change informaiton in cells B1, C1 or
D1
is it
possible to auto stamp the time and date of the add, update or
change in
cell
E1? If so can you help me understand how to write the formula?










  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Auto Stamp Date

How are the changes in A2:C2 being made?

The Sheet_Change code works only when a manual change is made in any of
A2:C2 cells.

I tested the code you posted under those circumstances and is OK.

If A2:C2 are formula-dreived values then you must use a Sheet_Calculate
event.

Private Sub Worksheet_Calculate()
code
End Sub


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 18:41:01 -0700, DataGuy
wrote:

It is funny that I found a post of the exact thing I need...posted on the
exact same day. Alan and Gord - I have followed your direction and posted
the code in VB but, nothing is appearing when I make a change to cells A2 to
C2 (my range is a little different). I have edited the code to suit my
needs. What am I doing wrong? My data is in cells A2:C2 and I want the
recorded change to post in D2.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A2:C2")) Is Nothing Then
Range("D2") = Now
Application.EnableEvents = True
End If
End Sub

"Gord Dibben" wrote:

Easiest method to add event code to a sheet is right-click on the sheet tab
and select "View Code"

Copy/paste the code into that module.

Edit if desired then Alt + q to go back to the Excel window.


Gord Dibben MS Excel MVP

On Thu, 26 Mar 2009 12:49:07 -0700, ShePink77
wrote:

Alan, thanks so much for the help. I have found the Alt F11 but where do I
copy and paste the below formula once the VBA Project window is open?

"Alan" wrote:

You can't do it with a formula because the NOW() function will update every
time the workbook calculates, not just when you alter B1:D1
This code will do it,

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B1:D1")) Is Nothing Then
Range("E1") = Now
Application.EnableEvents = True
End If
End Sub

To enter this,
Press Alt and F11 to open the VB editor
On the top left you'll see VBAProject with your filename in brackets
Expand that and double click Sheet1 or whatever sheet you want to do this on
Copy and paste the code into this new window, (watch or text wrap in the
email, there should be seven lines)
Press Alt and F11 again to close the VB editor
Save the file,

Regards,
Alan.


"ShePink77" wrote in message
...
I am creating a spreadsheet and would like to know if I am working in row 1
and I make add, update or change informaiton in cells B1, C1 or D1 is it
possible to auto stamp the time and date of the add, update or change in
cell
E1? If so can you help me understand how to write the formula?






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Separating date from a Date & Time stamp JT Excel Discussion (Misc queries) 9 June 10th 08 05:55 PM
Date Stamp Leon Excel Worksheet Functions 2 September 12th 07 02:07 PM
how do i auto insert a date and time stamp cjensen Excel Discussion (Misc queries) 3 February 6th 07 04:44 AM
Create a button that will date stamp todays date in a cell Tom Meacham Excel Discussion (Misc queries) 3 January 11th 06 02:08 AM
Date stamp spreadsheet in excel to remind me of completion date Big fella Excel Worksheet Functions 1 October 18th 05 04:10 PM


All times are GMT +1. The time now is 10:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"