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

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.
--
Gary''s Student - gsnu200831


"MikeZz" wrote:

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

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

MikeZz wrote:
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

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
"SaltyBall" wrote:

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

MikeZz wrote:
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

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" wrote:

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.
--
Gary''s Student - gsnu200831


"MikeZz" wrote:

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

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

--
Gary''s Student - gsnu200832


"MikeZz" wrote:

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" wrote:

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.
--
Gary''s Student - gsnu200831


"MikeZz" wrote:

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

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200832


"MikeZz" wrote:

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" wrote:

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.
--
Gary''s Student - gsnu200831


"MikeZz" wrote:

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

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




"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200832


"MikeZz" wrote:

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" wrote:

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.
--
Gary''s Student - gsnu200831


"MikeZz" wrote:

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

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" wrote:

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

--
Gary''s Student - gsnu200832


"MikeZz" wrote:

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" wrote:

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.
--
Gary''s Student - gsnu200831


"MikeZz" wrote:

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

Thanks for the feedback!
--
Gary''s Student - gsnu200832


"MikeZz" wrote:

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




"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200832


"MikeZz" wrote:

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" wrote:

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.
--
Gary''s Student - gsnu200831


"MikeZz" wrote:

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