Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamp Macro
Hey, all....In another post in these forums, a gent had posted a really slick
macro to insert a timestamp in a row, if any cells in that row changed. I "leveraged" that code (below). Problem is, it worked PERFECTLY for awhile, and then completely stopped. I have inserted Debug code right at the top of the subroutine, and it does not dump anything to the immediate window. I have no idea why the macro worked so well, then stopped. I have data in columns A through Z. My timestamp "was" going into column AA, until it crapped out. Any ideas? Thanks!, PatK Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target tr = t.Row Set r = Range("A:Z") If Intersect(t, r) Is Nothing Then Exit Sub Set rr = Range("A" & tr & ":Z" & tr) n = Application.WorksheetFunction.CountA(rr) Debug.Print n Application.EnableEvents = False If n = 0 Then Cells(tr, "AA").Clear End If If n = 1 Then Cells(tr, "AA").Value = Now End If Cells(tr, "AB") = Now Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamp Macro
Seems to work OK for me. Are you sure you are not in design mode when
testing it? It will not fire if you are. To check it, set a breakpoint before your debug.print line, make sure your design mode icon is not highlighted and make the worksheet active. Then make a change on the worksheet in columns a - z and see if it opens the VBE to the breakpoint line. If it does, the code is firing. "PatK" wrote: Hey, all....In another post in these forums, a gent had posted a really slick macro to insert a timestamp in a row, if any cells in that row changed. I "leveraged" that code (below). Problem is, it worked PERFECTLY for awhile, and then completely stopped. I have inserted Debug code right at the top of the subroutine, and it does not dump anything to the immediate window. I have no idea why the macro worked so well, then stopped. I have data in columns A through Z. My timestamp "was" going into column AA, until it crapped out. Any ideas? Thanks!, PatK Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target tr = t.Row Set r = Range("A:Z") If Intersect(t, r) Is Nothing Then Exit Sub Set rr = Range("A" & tr & ":Z" & tr) n = Application.WorksheetFunction.CountA(rr) Debug.Print n Application.EnableEvents = False If n = 0 Then Cells(tr, "AA").Clear End If If n = 1 Then Cells(tr, "AA").Value = Now End If Cells(tr, "AB") = Now Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamp Macro
Did it error out one time and leave events disabled?
In immediate window enter Application.EnableEvents = True then hit Enter key. If this was the cause, alter your code a bit to make sure events get turned back on in the case of an error. Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target tr = t.Row Set r = Range("A:Z") If Intersect(t, r) Is Nothing Then Exit Sub On Error GoTo endit 'add this Set rr = Range("A" & tr & ":Z" & tr) n = Application.WorksheetFunction.CountA(rr) Debug.Print n Application.EnableEvents = False If n = 0 Then Cells(tr, "AA").Clear End If If n = 1 Then Cells(tr, "AA").Value = Now End If Cells(tr, "AB") = Now endit: 'add this Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Thu, 30 Oct 2008 15:48:06 -0700, PatK wrote: Hey, all....In another post in these forums, a gent had posted a really slick macro to insert a timestamp in a row, if any cells in that row changed. I "leveraged" that code (below). Problem is, it worked PERFECTLY for awhile, and then completely stopped. I have inserted Debug code right at the top of the subroutine, and it does not dump anything to the immediate window. I have no idea why the macro worked so well, then stopped. I have data in columns A through Z. My timestamp "was" going into column AA, until it crapped out. Any ideas? Thanks!, PatK Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target tr = t.Row Set r = Range("A:Z") If Intersect(t, r) Is Nothing Then Exit Sub Set rr = Range("A" & tr & ":Z" & tr) n = Application.WorksheetFunction.CountA(rr) Debug.Print n Application.EnableEvents = False If n = 0 Then Cells(tr, "AA").Clear End If If n = 1 Then Cells(tr, "AA").Value = Now End If Cells(tr, "AB") = Now Application.EnableEvents = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamp Macro
I appreciate both your responses. I was not in design mode (in fact, did not
even know it existed...but I do now). I made sure I was out. I tried the breakpoint and it did not even act as if it go there. So, maybe I have a fundamental issue. To install that code, I opened up VBA, doubleclicked on the sheet I want the code in, and pasted that sub code into the window, then hit save. I am in completely the wrong place? Is there some other way that these events may be being shut off. Can't explain it..it worked so well at first, then I was off merrily pretty-ing up my worksheet, and came back to start adding data to the rows, and bang, it had stopped working. Frustated :-( patk "Gord Dibben" wrote: Did it error out one time and leave events disabled? In immediate window enter Application.EnableEvents = True then hit Enter key. If this was the cause, alter your code a bit to make sure events get turned back on in the case of an error. Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target tr = t.Row Set r = Range("A:Z") If Intersect(t, r) Is Nothing Then Exit Sub On Error GoTo endit 'add this Set rr = Range("A" & tr & ":Z" & tr) n = Application.WorksheetFunction.CountA(rr) Debug.Print n Application.EnableEvents = False If n = 0 Then Cells(tr, "AA").Clear End If If n = 1 Then Cells(tr, "AA").Value = Now End If Cells(tr, "AB") = Now endit: 'add this Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Thu, 30 Oct 2008 15:48:06 -0700, PatK wrote: Hey, all....In another post in these forums, a gent had posted a really slick macro to insert a timestamp in a row, if any cells in that row changed. I "leveraged" that code (below). Problem is, it worked PERFECTLY for awhile, and then completely stopped. I have inserted Debug code right at the top of the subroutine, and it does not dump anything to the immediate window. I have no idea why the macro worked so well, then stopped. I have data in columns A through Z. My timestamp "was" going into column AA, until it crapped out. Any ideas? Thanks!, PatK Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target tr = t.Row Set r = Range("A:Z") If Intersect(t, r) Is Nothing Then Exit Sub Set rr = Range("A" & tr & ":Z" & tr) n = Application.WorksheetFunction.CountA(rr) Debug.Print n Application.EnableEvents = False If n = 0 Then Cells(tr, "AA").Clear End If If n = 1 Then Cells(tr, "AA").Value = Now End If Cells(tr, "AB") = Now Application.EnableEvents = True End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamp Macro
Gord has pointed out another possibility. If the line that disables events
processes and for some reason the procedure does not complete, then when you make the next change, the events are still disabled. If you make the changes he suggested you would at least avoid that condition. Like I said, it worked like the code is written for me. the date/time first change is recorded in Column AA and then Column AB records the last change in the range for that line. If it helps, the way you described installing the code appeared to be correct. It goes in a worksheet code module for the sheet you want to track the changes on. It is possible that you were on the wrong sheet when you were trying to trigger it before. It only works for the sheet the code is in. I appreciate both your responses. I was not in design mode (in fact, did not even know it existed...but I do now). I made sure I was out. I tried the breakpoint and it did not even act as if it go there. So, maybe I have a fundamental issue. To install that code, I opened up VBA, doubleclicked on the sheet I want the code in, and pasted that sub code into the window, then hit save. I am in completely the wrong place? Is there some other way that these events may be being shut off. Can't explain it..it worked so well at first, then I was off merrily pretty-ing up my worksheet, and came back to start adding data to the rows, and bang, it had stopped working. Frustated :-( patk "Gord Dibben" wrote: Did it error out one time and leave events disabled? In immediate window enter Application.EnableEvents = True then hit Enter key. If this was the cause, alter your code a bit to make sure events get turned back on in the case of an error. Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target tr = t.Row Set r = Range("A:Z") If Intersect(t, r) Is Nothing Then Exit Sub On Error GoTo endit 'add this Set rr = Range("A" & tr & ":Z" & tr) n = Application.WorksheetFunction.CountA(rr) Debug.Print n Application.EnableEvents = False If n = 0 Then Cells(tr, "AA").Clear End If If n = 1 Then Cells(tr, "AA").Value = Now End If Cells(tr, "AB") = Now endit: 'add this Application.EnableEvents = True End Sub Gord Dibben MS Excel MVP On Thu, 30 Oct 2008 15:48:06 -0700, PatK wrote: Hey, all....In another post in these forums, a gent had posted a really slick macro to insert a timestamp in a row, if any cells in that row changed. I "leveraged" that code (below). Problem is, it worked PERFECTLY for awhile, and then completely stopped. I have inserted Debug code right at the top of the subroutine, and it does not dump anything to the immediate window. I have no idea why the macro worked so well, then stopped. I have data in columns A through Z. My timestamp "was" going into column AA, until it crapped out. Any ideas? Thanks!, PatK Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target tr = t.Row Set r = Range("A:Z") If Intersect(t, r) Is Nothing Then Exit Sub Set rr = Range("A" & tr & ":Z" & tr) n = Application.WorksheetFunction.CountA(rr) Debug.Print n Application.EnableEvents = False If n = 0 Then Cells(tr, "AA").Clear End If If n = 1 Then Cells(tr, "AA").Value = Now End If Cells(tr, "AB") = Now Application.EnableEvents = True End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamp Macro
Thanks...I guess we have a mystery. I did try to reset the event tracking in
the immediate window, and that did not work. I also incorporated the recommeneded changes Gord mentions. But it seems clear based upon a debug I put in as the very first line of code: I was not even getting to the code at all. So, I did this: I turned closed all excel files, cycled my computer, turned it back on, and it is working once more. Hmmmm. So, clearly the event "tracking" capability was turned off, and as you had noted, probably something interrupted the code originally due to lack of error trap. Keeping my finger's crossed that the issue is behind me. I am somewhat unclear of what is happening logically in the code, as there area couple of functions in it I am unfamiliar with. Your hint about what was happening between AA and AB columns was helpful (now, I just need to understand why you would not just write the last change to AB (instead of first to AA, then AB)? Ie, does this mean I better leave AA and AB clear? I know I want the timestamp in AB, but wondering about AA. Guess I will just hide that cell and I should be ok (don't want anyone writing accidently into a cell that this code is dependent up). Patk But I truly appreciate your help. Now I have to test to see if I can bust it againl. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamp Macro
I would assume that column AA is used to show if more than one change was
made. i.e. if the data in AB is different than AA then more than one change occured. Otherwise, you only know when the last change happened. The code is pretty simple. It checks to see if there are any entries on the row that the target cell is on. It counts the number of entries on that row and assigns that as the value of n. If there is only one, then it puts a date/time entry in col AA and then posts the last change date/time stamp in col AB. "PatK" wrote: Thanks...I guess we have a mystery. I did try to reset the event tracking in the immediate window, and that did not work. I also incorporated the recommeneded changes Gord mentions. But it seems clear based upon a debug I put in as the very first line of code: I was not even getting to the code at all. So, I did this: I turned closed all excel files, cycled my computer, turned it back on, and it is working once more. Hmmmm. So, clearly the event "tracking" capability was turned off, and as you had noted, probably something interrupted the code originally due to lack of error trap. Keeping my finger's crossed that the issue is behind me. I am somewhat unclear of what is happening logically in the code, as there area couple of functions in it I am unfamiliar with. Your hint about what was happening between AA and AB columns was helpful (now, I just need to understand why you would not just write the last change to AB (instead of first to AA, then AB)? Ie, does this mean I better leave AA and AB clear? I know I want the timestamp in AB, but wondering about AA. Guess I will just hide that cell and I should be ok (don't want anyone writing accidently into a cell that this code is dependent up). Patk But I truly appreciate your help. Now I have to test to see if I can bust it againl. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timestamp Macro
Thanks!!! So I am working for now....maybe it was an excel oddity!
"JLGWhiz" wrote: I would assume that column AA is used to show if more than one change was made. i.e. if the data in AB is different than AA then more than one change occured. Otherwise, you only know when the last change happened. The code is pretty simple. It checks to see if there are any entries on the row that the target cell is on. It counts the number of entries on that row and assigns that as the value of n. If there is only one, then it puts a date/time entry in col AA and then posts the last change date/time stamp in col AB. "PatK" wrote: Thanks...I guess we have a mystery. I did try to reset the event tracking in the immediate window, and that did not work. I also incorporated the recommeneded changes Gord mentions. But it seems clear based upon a debug I put in as the very first line of code: I was not even getting to the code at all. So, I did this: I turned closed all excel files, cycled my computer, turned it back on, and it is working once more. Hmmmm. So, clearly the event "tracking" capability was turned off, and as you had noted, probably something interrupted the code originally due to lack of error trap. Keeping my finger's crossed that the issue is behind me. I am somewhat unclear of what is happening logically in the code, as there area couple of functions in it I am unfamiliar with. Your hint about what was happening between AA and AB columns was helpful (now, I just need to understand why you would not just write the last change to AB (instead of first to AA, then AB)? Ie, does this mean I better leave AA and AB clear? I know I want the timestamp in AB, but wondering about AA. Guess I will just hide that cell and I should be ok (don't want anyone writing accidently into a cell that this code is dependent up). Patk But I truly appreciate your help. Now I have to test to see if I can bust it againl. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help with timestamp macro | Excel Discussion (Misc queries) | |||
Timestamp | Excel Discussion (Misc queries) | |||
Timestamp in log | Excel Programming | |||
Timestamp Macro | Excel Worksheet Functions | |||
macro for timestamp option button | Excel Programming |