Log who last changed a cell in a row...(incl Multi-Cell changes)

[MikeZz]

Hi,
I know it's been hashed through a number of times with the answer that you
can't track multi-cell changes.

My question is if I can at least record who changed a cell and when... don't
need to know the old or new value... only that it was changed.

In my case, I have an excel workbook with about 15000 rows of data.
I have data in columns A through H.
If someone changes the value either in Columns C or D in row 1234, I want to
put their name in cell I-1234 with today's date in cell J-1234.

I can get it to work for single cell changes but want to know if it's
possible to do this tracking on multi-cell changes as well. Examples
being... copy and paste to many cells, drag down fills, etcl. The reason I
have hope someone can help is that I don't need to know the before and after
values.

Thanks for any help,
MikeZz

 

[Gary''s Student]

The problem with logging changes to block of cells, is that it also requires
blocks of cells to do the logging.

What I suggest is that you loop through all the cells in Target (all the
cells that the Change Event sees as being changed) and add/modify a comment
to include the username and date.

 

[SaltyBall]

may be you need some database product because users can edit/delete the
cells you use to store the information.

 

[MikeZz]

Hi Salty,
Unfortunately a database won't work.
I'm doing this on a low budget (just my time) and I don't have much
experience in programming Access.

In addition, people who use the info are set in their ways.... meaning they
know excel and are comfortable with it. I will never get them and others to
go from excel to access because it would be way out of their comfort level.

But thanks for the feedback....
MikeZz

 

[MikeZz]

Hi Gary,
Thanks for the reply.

I looked through some code I had in another workbook that did the single
cell tracking but to be quite honest....
It's rather bulky (with a lot more than what I need).
It's been a year since I looked at it.
It was created from a hodge-podge of sources.

I don't think I would be able to trim it down easily without screwing
something up.

Your comments sound simple enough...

What I suggest is that you loop through all the cells in Target (all the
cells that the Change Event sees as being changed)

But I have very little experience in using change events and could probably
waste a couple hours figuring how to do it. So, do you know an online source
which has the basic syntax and structure to go through what you suggested?
Or maybe a simple routine that I could modify for my purposes?

Thanks again for the feedback.
MikeZz

 

[Gary''s Student]

Here is some demo event code that should be installed in the worksheet code
area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Messagee = Environ("username") & Chr(10) & Now
Application.EnableEvents = False
For Each r In t
r.NoteText Messagee
r.Comment.Visible = False
Next
End Sub

It will handle multiple cells as well as single cells. Because it is
worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[MikeZz]

Thanks for the help Gary.
I haven't tried your routine yet but I took a look at the Event Macros site
you suggested and I think it will really help explain things to me.

Thanks again,
MikeZz

 

[MikeZz]

Just a HUGE thanks Gary!
I combined your suggestion with intersect and it works like a charm...
And it's fast as well....

For anyone else... here's what I ended up with in the worksheet tab:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Range
Dim Messagee
Dim r As Range
Dim r1 As Range
Dim c1 As Range
Dim isect As Range
Dim NameCol
Dim datecol
NameCol = 10
datecol = 11

Set t = Target
Messagee = Environ("username") & Chr(10) & Now
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each r In t
Set r1 = Rows(r.Row)
Set c1 = Columns(NameCol)
Set isect = Application.Intersect(r1, c1)
isect = "My Name"
Set c1 = Columns(datecol)
Set isect = Application.Intersect(r1, c1)
isect = Date
Next
Application.ScreenUpdating = True
Application.EnableEvents = True
Set t = Nothing
Set r = Nothing
Set r1 = Nothing
Set c1 = Nothing
Set isect = Nothing
End Sub

 

[MikeZz]

One last modification...
Previous worked as long as there were no Autofilters set.
Added a couple lines and now it only updates rows that are visible in the
autofilter.

Thanks again!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim t As Range
Dim Messagee
Dim r As Range
Dim r1 As Range
Dim c1 As Range
Dim isect As Range
Dim NameCol
Dim datecol

NameCol = 10
datecol = 11

Set t = Target
Messagee = Environ("username") & Chr(10) & Now
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each r In t
Set r1 = Rows(r.Row)
If r1.EntireRow.Hidden = True Then GoTo nextR
Set c1 = Columns(NameCol)
Set isect = Application.Intersect(r1, c1)
isect = "My Name"
Set c1 = Columns(datecol)
Set isect = Application.Intersect(r1, c1)
isect = Now
nextR:
Next r
Application.ScreenUpdating = True
Application.EnableEvents = True
Set t = Nothing
Set r = Nothing
Set r1 = Nothing
Set c1 = Nothing
Set isect = Nothing
End Sub

 

[Gary''s Student]

Thanks for the feedback!