Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating date from a Date & Time stamp | Excel Discussion (Misc queries) | |||
Date Stamp | Excel Worksheet Functions | |||
how do i auto insert a date and time stamp | Excel Discussion (Misc queries) | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) | |||
Date stamp spreadsheet in excel to remind me of completion date | Excel Worksheet Functions |