Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Auto enter current date in cell when another changed

Is it possible to have excell automatically enter the date into a cell when
changes are made to another cell?
(I do not want this date to update so I don't think I can use the NOW or
TODAY functions).

Thank you.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Auto enter current date in cell when another changed

We can use an event macro to enter static dates. Say when data is intered
into column A we want the date inserted in column B right next to it. Insert
the following worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A:A")
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 1).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 - gsnu200812


"Andy Smith" wrote:

Is it possible to have excell automatically enter the date into a cell when
changes are made to another cell?
(I do not want this date to update so I don't think I can use the NOW or
TODAY functions).

Thank you.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 50
Default Auto enter current date in cell when another changed

Thanks Gary's Student, this is just what I needed. Works great and nice &
easy to follow. Only problem I have is when I insert another row into the
spreadsheet I get the following debug message:

Microsoft Visual Basic
Run-time error '1004':
Application-defined or object-defined error

This stops the Date being inserted until I restart excel. Any ideas?

"Gary''s Student" wrote:

We can use an event macro to enter static dates. Say when data is intered
into column A we want the date inserted in column B right next to it. Insert
the following worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A:A")
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 1).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 - gsnu200812


"Andy Smith" wrote:

Is it possible to have excell automatically enter the date into a cell when
changes are made to another cell?
(I do not want this date to update so I don't think I can use the NOW or
TODAY functions).

Thank you.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Auto enter current date in cell when another changed

We need to add one more line of code:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A:A")
If Target.Count1 Then Exit Sub
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 1).Value = Date
Application.EnableEvents = True
End Sub

--
Gary''s Student - gsnu200812


"Andy Smith" wrote:

Thanks Gary's Student, this is just what I needed. Works great and nice &
easy to follow. Only problem I have is when I insert another row into the
spreadsheet I get the following debug message:

Microsoft Visual Basic
Run-time error '1004':
Application-defined or object-defined error

This stops the Date being inserted until I restart excel. Any ideas?

"Gary''s Student" wrote:

We can use an event macro to enter static dates. Say when data is intered
into column A we want the date inserted in column B right next to it. Insert
the following worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A:A")
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 1).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 - gsnu200812


"Andy Smith" wrote:

Is it possible to have excell automatically enter the date into a cell when
changes are made to another cell?
(I do not want this date to update so I don't think I can use the NOW or
TODAY functions).

Thank you.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 50
Default Auto enter current date in cell when another changed

Perfect. Thats sorted that out.
Thanks for your help
Andy.

"Gary''s Student" wrote:

We need to add one more line of code:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A:A")
If Target.Count1 Then Exit Sub
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 1).Value = Date
Application.EnableEvents = True
End Sub

--
Gary''s Student - gsnu200812


"Andy Smith" wrote:

Thanks Gary's Student, this is just what I needed. Works great and nice &
easy to follow. Only problem I have is when I insert another row into the
spreadsheet I get the following debug message:

Microsoft Visual Basic
Run-time error '1004':
Application-defined or object-defined error

This stops the Date being inserted until I restart excel. Any ideas?

"Gary''s Student" wrote:

We can use an event macro to enter static dates. Say when data is intered
into column A we want the date inserted in column B right next to it. Insert
the following worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A:A")
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 1).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 - gsnu200812


"Andy Smith" wrote:

Is it possible to have excell automatically enter the date into a cell when
changes are made to another cell?
(I do not want this date to update so I don't think I can use the NOW or
TODAY functions).

Thank you.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Auto enter current date in cell when another changed


This works great! I'm a complete novice at VB and macros. How can I
make it work for a number of column pairs in a spreadsheet? In addition
to entering the date in column B when column A is updated, I need to
update column D when column C is updated, and so on. Ultimately, I'll
need this about 9 times across my spreadsheet. Thanks.


--
sbird
------------------------------------------------------------------------
sbird's Profile: http://www.thecodecage.com/forumz/member.php?userid=445
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=26580

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default Auto enter current date in cell when another changed

Hi,

Please enter your question in the body of the email

Here is a macro

Here is some sample code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then
[D1] = Date
End If
End Sub

Press Alt+F11 and on the top left locate your file and then the sheet where
you want this feature. Double click the sheet and paste in the above code.
In the exampe above we are checking to see if A1 has changed and then
entering todays date in D1.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"unknown" wrote:


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default Auto enter current date in cell when another changed

What works great?

To whom or what are you responding?

Post some of the original posting or a message ID


Gord Dibben MS Excel MVP

On Wed, 24 Jun 2009 19:12:43 +0100, sbird
wrote:


This works great! I'm a complete novice at VB and macros. How can I
make it work for a number of column pairs in a spreadsheet? In addition
to entering the date in column B when column A is updated, I need to
update column D when column C is updated, and so on. Ultimately, I'll
need this about 9 times across my spreadsheet. Thanks.


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
How to auto insert date in one cell when another is changed Jim Excel Worksheet Functions 1 September 5th 08 12:02 PM
How to make the current date appear when i click 'enter' in a cell Matt_07 Excel Worksheet Functions 1 January 30th 07 10:32 AM
Auto enter date when data in enter in another cell Brian Excel Worksheet Functions 5 December 7th 06 07:44 PM
WHAT FUNCTION 2 ENTER SO THE CELL TURNS RED AFTER CURRENT DATE awilliams Excel Worksheet Functions 4 June 1st 06 09:40 PM
enter a current date in a cell? Tpeyton77 Excel Discussion (Misc queries) 2 June 8th 05 02:30 AM


All times are GMT +1. The time now is 01:05 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"