Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() Hi all, I am using office 2003; I am trying to fix up a automated time tracking system. I like to find when the particular field is updated in the excel and update the same in the adjacent row. Let me give you a example Field A5 will be filled as Yes / No I want the A6 to be filled with the date and time of the field A5 got updated I tried with this formula: *@ A6 *I typed -= IF(ISBLANK(A5)=FALSE,NOW()," ") but the problem is this when ever I save the excel the A6 is getting updated with the current time. With regards Pradeep -- Qlychap ------------------------------------------------------------------------ Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253 View this thread: http://www.excelforum.com/showthread...hreadid=550268 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes: http://www.mcgimpsey.com/excel/timestamp.html Qlychap wrote: Hi all, I am using office 2003; I am trying to fix up a automated time tracking system. I like to find when the particular field is updated in the excel and update the same in the adjacent row. Let me give you a example Field A5 will be filled as Yes / No I want the A6 to be filled with the date and time of the field A5 got updated I tried with this formula: *@ A6 *I typed -= IF(ISBLANK(A5)=FALSE,NOW()," ") but the problem is this when ever I save the excel the A6 is getting updated with the current time. With regards Pradeep -- Qlychap ------------------------------------------------------------------------ Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253 View this thread: http://www.excelforum.com/showthread...hreadid=550268 -- Dave Peterson |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() Hi Paste the code onto sheet module 1) right click on the sheet tab in question and select [ViewCode] 2) paste the code onto the blank space on the right pane 3) click x to close the window and get back to Excel It will change the time stamp when the data actually changed Code: -------------------- Private OldData Private Sub Sheet_SelectionChange(ByVal Target As Range) OldData = Target.Value End Sub Private Sub Worksheet_Change(ByVal Target As Range) With Target.Cells(1,1) If .Address(0,0) < "A5" Then Exit Sub If .Value = OldData Then Exit Sub Application.EnableEvents = False .Offset(1).Value = Now Application.EnableEvents = True OldData = .Value End With End Sub -------------------- -- jindon ------------------------------------------------------------------------ jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135 View this thread: http://www.excelforum.com/showthread...hreadid=550268 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I used the McGimpsey code, and it worked, until I protected the sheet, then
it quit working entirely. I unprotected the 2 columns in the formula, but it made no difference. Is there a trick to using it in a protected sheet? Richard "Dave Peterson" wrote: J.E. McGimpsey shows a way to put a time stamp on the same row when something changes: http://www.mcgimpsey.com/excel/timestamp.html Qlychap wrote: Hi all, I am using office 2003; I am trying to fix up a automated time tracking system. I like to find when the particular field is updated in the excel and update the same in the adjacent row. Let me give you a example Field A5 will be filled as Yes / No I want the A6 to be filled with the date and time of the field A5 got updated I tried with this formula: *@ A6 *I typed -= IF(ISBLANK(A5)=FALSE,NOW()," ") but the problem is this when ever I save the excel the A6 is getting updated with the current time. With regards Pradeep -- Qlychap ------------------------------------------------------------------------ Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253 View this thread: http://www.excelforum.com/showthread...hreadid=550268 -- Dave Peterson |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
First, unprotect the worksheet.
then format the column getting the time/date the way you want. Then protect the worksheet once again. And remove the formatting from the code. Changing the format of a cell on a protected sheet could be causing your trouble. If that doesn't work, post the code you used and indicate the line that fails. Richard R wrote: I used the McGimpsey code, and it worked, until I protected the sheet, then it quit working entirely. I unprotected the 2 columns in the formula, but it made no difference. Is there a trick to using it in a protected sheet? Richard "Dave Peterson" wrote: J.E. McGimpsey shows a way to put a time stamp on the same row when something changes: http://www.mcgimpsey.com/excel/timestamp.html Qlychap wrote: Hi all, I am using office 2003; I am trying to fix up a automated time tracking system. I like to find when the particular field is updated in the excel and update the same in the adjacent row. Let me give you a example Field A5 will be filled as Yes / No I want the A6 to be filled with the date and time of the field A5 got updated I tried with this formula: *@ A6 *I typed -= IF(ISBLANK(A5)=FALSE,NOW()," ") but the problem is this when ever I save the excel the A6 is getting updated with the current time. With regards Pradeep -- Qlychap ------------------------------------------------------------------------ Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253 View this thread: http://www.excelforum.com/showthread...hreadid=550268 -- Dave Peterson -- Dave Peterson |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I unprotected the sheet, formatted the coulmn, inserted the code, and it
worked fine. I protected the sheet, and the code stopped running at ..NumberFormat = "dd mmm yyyy hh:mm:ss". Even better, if I then unprotect the sheet, not only do I not get an error, but nothing happens at all. I would like to insert the date in mm/dd/yy format. Code follows Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F2:F950"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Date End With End If Application.EnableEvents = True End If End With End Sub Thanks Richard "Dave Peterson" wrote: First, unprotect the worksheet. then format the column getting the time/date the way you want. Then protect the worksheet once again. And remove the formatting from the code. Changing the format of a cell on a protected sheet could be causing your trouble. If that doesn't work, post the code you used and indicate the line that fails. Richard R wrote: I used the McGimpsey code, and it worked, until I protected the sheet, then it quit working entirely. I unprotected the 2 columns in the formula, but it made no difference. Is there a trick to using it in a protected sheet? Richard "Dave Peterson" wrote: J.E. McGimpsey shows a way to put a time stamp on the same row when something changes: http://www.mcgimpsey.com/excel/timestamp.html Qlychap wrote: Hi all, I am using office 2003; I am trying to fix up a automated time tracking system. I like to find when the particular field is updated in the excel and update the same in the adjacent row. Let me give you a example Field A5 will be filled as Yes / No I want the A6 to be filled with the date and time of the field A5 got updated I tried with this formula: *@ A6 *I typed -= IF(ISBLANK(A5)=FALSE,NOW()," ") but the problem is this when ever I save the excel the A6 is getting updated with the current time. With regards Pradeep -- Qlychap ------------------------------------------------------------------------ Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253 View this thread: http://www.excelforum.com/showthread...hreadid=550268 -- Dave Peterson -- Dave Peterson |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You skipped this line:
And remove the formatting from the code. Just delete that .numberformat line from your code. And format that column G as mm/dd/yyyy (manually). Richard R wrote: I unprotected the sheet, formatted the coulmn, inserted the code, and it worked fine. I protected the sheet, and the code stopped running at .NumberFormat = "dd mmm yyyy hh:mm:ss". Even better, if I then unprotect the sheet, not only do I not get an error, but nothing happens at all. I would like to insert the date in mm/dd/yy format. Code follows Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F2:F950"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Date End With End If Application.EnableEvents = True End If End With End Sub Thanks Richard "Dave Peterson" wrote: First, unprotect the worksheet. then format the column getting the time/date the way you want. Then protect the worksheet once again. And remove the formatting from the code. Changing the format of a cell on a protected sheet could be causing your trouble. If that doesn't work, post the code you used and indicate the line that fails. Richard R wrote: I used the McGimpsey code, and it worked, until I protected the sheet, then it quit working entirely. I unprotected the 2 columns in the formula, but it made no difference. Is there a trick to using it in a protected sheet? Richard "Dave Peterson" wrote: J.E. McGimpsey shows a way to put a time stamp on the same row when something changes: http://www.mcgimpsey.com/excel/timestamp.html Qlychap wrote: Hi all, I am using office 2003; I am trying to fix up a automated time tracking system. I like to find when the particular field is updated in the excel and update the same in the adjacent row. Let me give you a example Field A5 will be filled as Yes / No I want the A6 to be filled with the date and time of the field A5 got updated I tried with this formula: *@ A6 *I typed -= IF(ISBLANK(A5)=FALSE,NOW()," ") but the problem is this when ever I save the excel the A6 is getting updated with the current time. With regards Pradeep -- Qlychap ------------------------------------------------------------------------ Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253 View this thread: http://www.excelforum.com/showthread...hreadid=550268 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I wondered what you meant by removing the formatting from the code. I didn't
format the code! :<) It still didn't work with that change, but it was in a book with 5 other sheets. I put it into a book by itself, and it seems to work OK now. It must have something to do with modules or some other esoteric VBA stuff. Anyway, it works now. Thanks greatly, Richard "Dave Peterson" wrote: You skipped this line: And remove the formatting from the code. Just delete that .numberformat line from your code. And format that column G as mm/dd/yyyy (manually). Richard R wrote: I unprotected the sheet, formatted the coulmn, inserted the code, and it worked fine. I protected the sheet, and the code stopped running at .NumberFormat = "dd mmm yyyy hh:mm:ss". Even better, if I then unprotect the sheet, not only do I not get an error, but nothing happens at all. I would like to insert the date in mm/dd/yy format. Code follows Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F2:F950"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Date End With End If Application.EnableEvents = True End If End With End Sub Thanks Richard "Dave Peterson" wrote: First, unprotect the worksheet. then format the column getting the time/date the way you want. Then protect the worksheet once again. And remove the formatting from the code. Changing the format of a cell on a protected sheet could be causing your trouble. If that doesn't work, post the code you used and indicate the line that fails. Richard R wrote: I used the McGimpsey code, and it worked, until I protected the sheet, then it quit working entirely. I unprotected the 2 columns in the formula, but it made no difference. Is there a trick to using it in a protected sheet? Richard "Dave Peterson" wrote: J.E. McGimpsey shows a way to put a time stamp on the same row when something changes: http://www.mcgimpsey.com/excel/timestamp.html Qlychap wrote: Hi all, I am using office 2003; I am trying to fix up a automated time tracking system. I like to find when the particular field is updated in the excel and update the same in the adjacent row. Let me give you a example Field A5 will be filled as Yes / No I want the A6 to be filled with the date and time of the field A5 got updated I tried with this formula: *@ A6 *I typed -= IF(ISBLANK(A5)=FALSE,NOW()," ") but the problem is this when ever I save the excel the A6 is getting updated with the current time. With regards Pradeep -- Qlychap ------------------------------------------------------------------------ Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253 View this thread: http://www.excelforum.com/showthread...hreadid=550268 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I spoke too soon. I saved the file by itself, then reopened the file, and it
now does nothing again. I viewed the code, and it is still exactly as you suggested, but it does nothing when I change the subject cells. -- Richard "Richard R" wrote: I wondered what you meant by removing the formatting from the code. I didn't format the code! :<) It still didn't work with that change, but it was in a book with 5 other sheets. I put it into a book by itself, and it seems to work OK now. It must have something to do with modules or some other esoteric VBA stuff. Anyway, it works now. Thanks greatly, Richard "Dave Peterson" wrote: You skipped this line: And remove the formatting from the code. Just delete that .numberformat line from your code. And format that column G as mm/dd/yyyy (manually). Richard R wrote: I unprotected the sheet, formatted the coulmn, inserted the code, and it worked fine. I protected the sheet, and the code stopped running at .NumberFormat = "dd mmm yyyy hh:mm:ss". Even better, if I then unprotect the sheet, not only do I not get an error, but nothing happens at all. I would like to insert the date in mm/dd/yy format. Code follows Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F2:F950"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Date End With End If Application.EnableEvents = True End If End With End Sub Thanks Richard "Dave Peterson" wrote: First, unprotect the worksheet. then format the column getting the time/date the way you want. Then protect the worksheet once again. And remove the formatting from the code. Changing the format of a cell on a protected sheet could be causing your trouble. If that doesn't work, post the code you used and indicate the line that fails. Richard R wrote: I used the McGimpsey code, and it worked, until I protected the sheet, then it quit working entirely. I unprotected the 2 columns in the formula, but it made no difference. Is there a trick to using it in a protected sheet? Richard "Dave Peterson" wrote: J.E. McGimpsey shows a way to put a time stamp on the same row when something changes: http://www.mcgimpsey.com/excel/timestamp.html Qlychap wrote: Hi all, I am using office 2003; I am trying to fix up a automated time tracking system. I like to find when the particular field is updated in the excel and update the same in the adjacent row. Let me give you a example Field A5 will be filled as Yes / No I want the A6 to be filled with the date and time of the field A5 got updated I tried with this formula: *@ A6 *I typed -= IF(ISBLANK(A5)=FALSE,NOW()," ") but the problem is this when ever I save the excel the A6 is getting updated with the current time. With regards Pradeep -- Qlychap ------------------------------------------------------------------------ Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253 View this thread: http://www.excelforum.com/showthread...hreadid=550268 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You may have turned event handling off.
Close excel and reopen it and your workbook. Or... hit alt-f11 to get to the VBE hit ctrl-g to see the immediate window type this and hit enter: application.enableevents = true Then back to excel to test it out. Richard R wrote: I spoke too soon. I saved the file by itself, then reopened the file, and it now does nothing again. I viewed the code, and it is still exactly as you suggested, but it does nothing when I change the subject cells. -- Richard "Richard R" wrote: I wondered what you meant by removing the formatting from the code. I didn't format the code! :<) It still didn't work with that change, but it was in a book with 5 other sheets. I put it into a book by itself, and it seems to work OK now. It must have something to do with modules or some other esoteric VBA stuff. Anyway, it works now. Thanks greatly, Richard "Dave Peterson" wrote: You skipped this line: And remove the formatting from the code. Just delete that .numberformat line from your code. And format that column G as mm/dd/yyyy (manually). Richard R wrote: I unprotected the sheet, formatted the coulmn, inserted the code, and it worked fine. I protected the sheet, and the code stopped running at .NumberFormat = "dd mmm yyyy hh:mm:ss". Even better, if I then unprotect the sheet, not only do I not get an error, but nothing happens at all. I would like to insert the date in mm/dd/yy format. Code follows Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F2:F950"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Date End With End If Application.EnableEvents = True End If End With End Sub Thanks Richard "Dave Peterson" wrote: First, unprotect the worksheet. then format the column getting the time/date the way you want. Then protect the worksheet once again. And remove the formatting from the code. Changing the format of a cell on a protected sheet could be causing your trouble. If that doesn't work, post the code you used and indicate the line that fails. Richard R wrote: I used the McGimpsey code, and it worked, until I protected the sheet, then it quit working entirely. I unprotected the 2 columns in the formula, but it made no difference. Is there a trick to using it in a protected sheet? Richard "Dave Peterson" wrote: J.E. McGimpsey shows a way to put a time stamp on the same row when something changes: http://www.mcgimpsey.com/excel/timestamp.html Qlychap wrote: Hi all, I am using office 2003; I am trying to fix up a automated time tracking system. I like to find when the particular field is updated in the excel and update the same in the adjacent row. Let me give you a example Field A5 will be filled as Yes / No I want the A6 to be filled with the date and time of the field A5 got updated I tried with this formula: *@ A6 *I typed -= IF(ISBLANK(A5)=FALSE,NOW()," ") but the problem is this when ever I save the excel the A6 is getting updated with the current time. With regards Pradeep -- Qlychap ------------------------------------------------------------------------ Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253 View this thread: http://www.excelforum.com/showthread...hreadid=550268 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I did that and when I hit enter I got an error message that macros were
turned off. -- Richard "Dave Peterson" wrote: You may have turned event handling off. Close excel and reopen it and your workbook. Or... hit alt-f11 to get to the VBE hit ctrl-g to see the immediate window type this and hit enter: application.enableevents = true Then back to excel to test it out. Richard R wrote: I spoke too soon. I saved the file by itself, then reopened the file, and it now does nothing again. I viewed the code, and it is still exactly as you suggested, but it does nothing when I change the subject cells. -- Richard "Richard R" wrote: I wondered what you meant by removing the formatting from the code. I didn't format the code! :<) It still didn't work with that change, but it was in a book with 5 other sheets. I put it into a book by itself, and it seems to work OK now. It must have something to do with modules or some other esoteric VBA stuff. Anyway, it works now. Thanks greatly, Richard "Dave Peterson" wrote: You skipped this line: And remove the formatting from the code. Just delete that .numberformat line from your code. And format that column G as mm/dd/yyyy (manually). Richard R wrote: I unprotected the sheet, formatted the coulmn, inserted the code, and it worked fine. I protected the sheet, and the code stopped running at .NumberFormat = "dd mmm yyyy hh:mm:ss". Even better, if I then unprotect the sheet, not only do I not get an error, but nothing happens at all. I would like to insert the date in mm/dd/yy format. Code follows Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F2:F950"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Date End With End If Application.EnableEvents = True End If End With End Sub Thanks Richard "Dave Peterson" wrote: First, unprotect the worksheet. then format the column getting the time/date the way you want. Then protect the worksheet once again. And remove the formatting from the code. Changing the format of a cell on a protected sheet could be causing your trouble. If that doesn't work, post the code you used and indicate the line that fails. Richard R wrote: I used the McGimpsey code, and it worked, until I protected the sheet, then it quit working entirely. I unprotected the 2 columns in the formula, but it made no difference. Is there a trick to using it in a protected sheet? Richard "Dave Peterson" wrote: J.E. McGimpsey shows a way to put a time stamp on the same row when something changes: http://www.mcgimpsey.com/excel/timestamp.html Qlychap wrote: Hi all, I am using office 2003; I am trying to fix up a automated time tracking system. I like to find when the particular field is updated in the excel and update the same in the adjacent row. Let me give you a example Field A5 will be filled as Yes / No I want the A6 to be filled with the date and time of the field A5 got updated I tried with this formula: *@ A6 *I typed -= IF(ISBLANK(A5)=FALSE,NOW()," ") but the problem is this when ever I save the excel the A6 is getting updated with the current time. With regards Pradeep -- Qlychap ------------------------------------------------------------------------ Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253 View this thread: http://www.excelforum.com/showthread...hreadid=550268 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Make sure your security level is not set for High (medium or low is ok).
Tools|Macro|Security|Macro Security (medium will give you a warning prompt and MS recommends against low) Then close your workbook and reopen it -- this setting isn't retroactive. If you chose medium, make sure you said yes to allow macros. Richard R wrote: I did that and when I hit enter I got an error message that macros were turned off. -- Richard "Dave Peterson" wrote: You may have turned event handling off. Close excel and reopen it and your workbook. Or... hit alt-f11 to get to the VBE hit ctrl-g to see the immediate window type this and hit enter: application.enableevents = true Then back to excel to test it out. Richard R wrote: I spoke too soon. I saved the file by itself, then reopened the file, and it now does nothing again. I viewed the code, and it is still exactly as you suggested, but it does nothing when I change the subject cells. -- Richard "Richard R" wrote: I wondered what you meant by removing the formatting from the code. I didn't format the code! :<) It still didn't work with that change, but it was in a book with 5 other sheets. I put it into a book by itself, and it seems to work OK now. It must have something to do with modules or some other esoteric VBA stuff. Anyway, it works now. Thanks greatly, Richard "Dave Peterson" wrote: You skipped this line: And remove the formatting from the code. Just delete that .numberformat line from your code. And format that column G as mm/dd/yyyy (manually). Richard R wrote: I unprotected the sheet, formatted the coulmn, inserted the code, and it worked fine. I protected the sheet, and the code stopped running at .NumberFormat = "dd mmm yyyy hh:mm:ss". Even better, if I then unprotect the sheet, not only do I not get an error, but nothing happens at all. I would like to insert the date in mm/dd/yy format. Code follows Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F2:F950"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Date End With End If Application.EnableEvents = True End If End With End Sub Thanks Richard "Dave Peterson" wrote: First, unprotect the worksheet. then format the column getting the time/date the way you want. Then protect the worksheet once again. And remove the formatting from the code. Changing the format of a cell on a protected sheet could be causing your trouble. If that doesn't work, post the code you used and indicate the line that fails. Richard R wrote: I used the McGimpsey code, and it worked, until I protected the sheet, then it quit working entirely. I unprotected the 2 columns in the formula, but it made no difference. Is there a trick to using it in a protected sheet? Richard "Dave Peterson" wrote: J.E. McGimpsey shows a way to put a time stamp on the same row when something changes: http://www.mcgimpsey.com/excel/timestamp.html Qlychap wrote: Hi all, I am using office 2003; I am trying to fix up a automated time tracking system. I like to find when the particular field is updated in the excel and update the same in the adjacent row. Let me give you a example Field A5 will be filled as Yes / No I want the A6 to be filled with the date and time of the field A5 got updated I tried with this formula: *@ A6 *I typed -= IF(ISBLANK(A5)=FALSE,NOW()," ") but the problem is this when ever I save the excel the A6 is getting updated with the current time. With regards Pradeep -- Qlychap ------------------------------------------------------------------------ Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253 View this thread: http://www.excelforum.com/showthread...hreadid=550268 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Events enabled. Security set to medium. Saved and reopened. Dialog to allow
macros "Yes". Nothing happens when I make a change. I am tired of messing with it, and it is not absolutely necessary for it to happen. I truely appreciate all your help and how difficult it is to fix something by "remote control." I won't take up any more of your time. Have a good weekend. -- Richard "Dave Peterson" wrote: Make sure your security level is not set for High (medium or low is ok). Tools|Macro|Security|Macro Security (medium will give you a warning prompt and MS recommends against low) Then close your workbook and reopen it -- this setting isn't retroactive. If you chose medium, make sure you said yes to allow macros. Richard R wrote: I did that and when I hit enter I got an error message that macros were turned off. -- Richard "Dave Peterson" wrote: You may have turned event handling off. Close excel and reopen it and your workbook. Or... hit alt-f11 to get to the VBE hit ctrl-g to see the immediate window type this and hit enter: application.enableevents = true Then back to excel to test it out. Richard R wrote: I spoke too soon. I saved the file by itself, then reopened the file, and it now does nothing again. I viewed the code, and it is still exactly as you suggested, but it does nothing when I change the subject cells. -- Richard "Richard R" wrote: I wondered what you meant by removing the formatting from the code. I didn't format the code! :<) It still didn't work with that change, but it was in a book with 5 other sheets. I put it into a book by itself, and it seems to work OK now. It must have something to do with modules or some other esoteric VBA stuff. Anyway, it works now. Thanks greatly, Richard "Dave Peterson" wrote: You skipped this line: And remove the formatting from the code. Just delete that .numberformat line from your code. And format that column G as mm/dd/yyyy (manually). Richard R wrote: I unprotected the sheet, formatted the coulmn, inserted the code, and it worked fine. I protected the sheet, and the code stopped running at .NumberFormat = "dd mmm yyyy hh:mm:ss". Even better, if I then unprotect the sheet, not only do I not get an error, but nothing happens at all. I would like to insert the date in mm/dd/yy format. Code follows Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("F2:F950"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Date End With End If Application.EnableEvents = True End If End With End Sub Thanks Richard "Dave Peterson" wrote: First, unprotect the worksheet. then format the column getting the time/date the way you want. Then protect the worksheet once again. And remove the formatting from the code. Changing the format of a cell on a protected sheet could be causing your trouble. If that doesn't work, post the code you used and indicate the line that fails. Richard R wrote: I used the McGimpsey code, and it worked, until I protected the sheet, then it quit working entirely. I unprotected the 2 columns in the formula, but it made no difference. Is there a trick to using it in a protected sheet? Richard "Dave Peterson" wrote: J.E. McGimpsey shows a way to put a time stamp on the same row when something changes: http://www.mcgimpsey.com/excel/timestamp.html Qlychap wrote: Hi all, I am using office 2003; I am trying to fix up a automated time tracking system. I like to find when the particular field is updated in the excel and update the same in the adjacent row. Let me give you a example Field A5 will be filled as Yes / No I want the A6 to be filled with the date and time of the field A5 got updated I tried with this formula: *@ A6 *I typed -= IF(ISBLANK(A5)=FALSE,NOW()," ") but the problem is this when ever I save the excel the A6 is getting updated with the current time. With regards Pradeep -- Qlychap ------------------------------------------------------------------------ Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253 View this thread: http://www.excelforum.com/showthread...hreadid=550268 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
excel, automatic date and time when info gets entered | Excel Worksheet Functions | |||
Combine Date & Time Cells | Excel Discussion (Misc queries) | |||
Using VLOOKUP with a Date and Time | Excel Discussion (Misc queries) | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
Imported Date & Time format with calcs. managed in excel from imrp | Excel Worksheet Functions |