Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to auto insert date in one cell when another is changed | Excel Worksheet Functions | |||
How to make the current date appear when i click 'enter' in a cell | Excel Worksheet Functions | |||
Auto enter date when data in enter in another cell | Excel Worksheet Functions | |||
WHAT FUNCTION 2 ENTER SO THE CELL TURNS RED AFTER CURRENT DATE | Excel Worksheet Functions | |||
enter a current date in a cell? | Excel Discussion (Misc queries) |