Timestamp Macro

[PatK]

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

 

[JLGWhiz]

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.

 

[Gord Dibben]

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

 

[PatK]

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

 

[JLGWhiz]

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.

 

[PatK]

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.

 

[JLGWhiz]

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]

Thanks!!! So I am working for now....maybe it was an excel oddity!