Hi,

Is there a way to track changes made to a worksheet using VBA?

I was thinking that perhaps there was an event procedure. I envision
that the change could be displayed on a hidden sheet when a change is
made - similar to the tracking changes tool but without sharing a workbook.

I'm on a deadline with this one and so any help would be greatly
appreciated. I've asked about this issue before and haven't received
any replies and so I'm thinking that maybe this cannot be done.

Thanks in advance for any help,
anita

Anita,

No problem, use the Worksheet_Change event in the code for the sheet you
want to "track" (right click on the sheet tab and select the menu item to
add code to that sheet)


The code below writes the change to a sheet called "Log"
The row used to write the last change is stored in cell B1 and is
incremented each time the event fires.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Lastrow As Long
Lastrow = ActiveWorkbook.Sheets("Log").Range("B1").Value

'Write the Change
ActiveWorkbook.Sheets("Log").Cells(Lastrow + 1, 1) = "Sheet (name) - Row " &
Target.Row & " - Column " & Target.Column & " changed to: " & Target.Value

'Increase the LastRow value by 1
ActiveWorkbook.Sheets("Log").Range("B1").Value = Lastrow + 1

End Sub




Good luck


"a" wrote in message
link.net...
Hi,

Is there a way to track changes made to a worksheet using VBA?

I was thinking that perhaps there was an event procedure. I envision
that the change could be displayed on a hidden sheet when a change is
made - similar to the tracking changes tool but without sharing a
workbook.

I'm on a deadline with this one and so any help would be greatly
appreciated. I've asked about this issue before and haven't received
any replies and so I'm thinking that maybe this cannot be done.

Thanks in advance for any help,
anita

Anita,

No problem, use the Worksheet_Change event in the code for the sheet you
want to "track" (right click on the sheet tab and select the menu item to
add code to that sheet)


The code below writes the change to a sheet called "Log"
The row used to write the last change is stored in cell B1 and is
incremented each time the event fires.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Lastrow As Long
Lastrow = ActiveWorkbook.Sheets("Log").Range("B1").Value

'Write the Change
ActiveWorkbook.Sheets("Log").Cells(Lastrow + 1, 1) = "Sheet (name) - Row " &
Target.Row & " - Column " & Target.Column & " changed to: " & Target.Value

'Increase the LastRow value by 1
ActiveWorkbook.Sheets("Log").Range("B1").Value = Lastrow + 1

End Sub




Good luck

"a" wrote in message
link.net...
Hi,

Is there a way to track changes made to a worksheet using VBA?

I was thinking that perhaps there was an event procedure. I envision
that the change could be displayed on a hidden sheet when a change is
made - similar to the tracking changes tool but without sharing a
workbook.

I'm on a deadline with this one and so any help would be greatly
appreciated. I've asked about this issue before and haven't received
any replies and so I'm thinking that maybe this cannot be done.

Thanks in advance for any help,
anita

Dear Binzelli,

Thanks much! This looks like it just may be what I'm looking for. I'm
going to try it as soon as I get in to work.

Thanks again,
Anita

Binzelli wrote:

Anita,

No problem, use the Worksheet_Change event in the code for the sheet you
want to "track" (right click on the sheet tab and select the menu item to
add code to that sheet)


The code below writes the change to a sheet called "Log"
The row used to write the last change is stored in cell B1 and is
incremented each time the event fires.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Lastrow As Long
Lastrow = ActiveWorkbook.Sheets("Log").Range("B1").Value

'Write the Change
ActiveWorkbook.Sheets("Log").Cells(Lastrow + 1, 1) = "Sheet (name) - Row " &
Target.Row & " - Column " & Target.Column & " changed to: " & Target.Value

'Increase the LastRow value by 1
ActiveWorkbook.Sheets("Log").Range("B1").Value = Lastrow + 1

End Sub




Good luck

"a" wrote in message
link.net...

Hi,

Is there a way to track changes made to a worksheet using VBA?

I was thinking that perhaps there was an event procedure. I envision
that the change could be displayed on a hidden sheet when a change is
made - similar to the tracking changes tool but without sharing a

workbook.

I'm on a deadline with this one and so any help would be greatly
appreciated. I've asked about this issue before and haven't received
any replies and so I'm thinking that maybe this cannot be done.

Thanks in advance for any help,
anita

Dear Anita, I will appreciate if you allow me to can make
a comment on your request.

Dear Binzelli,

It looks it won't work on the following cases:
1. When updating several cells at the same time
(selecting range, and pressing Ctrl+Enter and input the
same value into every selected cell).
2. When writing formulas. The Log will reflect the value
not the formula.
3. When changing any value on a table, formulas will be
updated and hence changes on those (formula) cells will
be reflected on the Log sheet (although they should not
be there since these cells were not actually changed,
only the predecessors).

Is there a way to make work with these restrictions?

Sorry it this is too much :-(

Thanks for your reply.
-----Original Message-----
Dear Binzelli,

Thanks much! This looks like it just may be what I'm
looking for. I'm
going to try it as soon as I get in to work.

Thanks again,
Anita

Binzelli wrote:

Anita,

No problem, use the Worksheet_Change event in the code
for the sheet you
want to "track" (right click on the sheet tab and
select the menu item to
add code to that sheet)


The code below writes the change to a sheet
called "Log"
The row used to write the last change is stored in
cell B1 and is
incremented each time the event fires.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Lastrow As Long
Lastrow = ActiveWorkbook.Sheets("Log").Range
("B1").Value

'Write the Change
ActiveWorkbook.Sheets("Log").Cells(Lastrow + 1, 1)
= "Sheet (name) - Row " &
Target.Row & " - Column " & Target.Column & " changed
to: " & Target.Value

'Increase the LastRow value by 1
ActiveWorkbook.Sheets("Log").Range("B1").Value =
Lastrow + 1

End Sub




Good luck

"a" wrote in message
news:d9gCc.11261
...

Hi,

Is there a way to track changes made to a worksheet
using VBA?

I was thinking that perhaps there was an event
procedure. I envision
that the change could be displayed on a hidden sheet
when a change is
made - similar to the tracking changes tool but
without sharing a

workbook.

I'm on a deadline with this one and so any help would
be greatly
appreciated. I've asked about this issue before and
haven't received
any replies and so I'm thinking that maybe this cannot
be done.

Thanks in advance for any help,
anita






.

Hi !

Your first 'problem' is solved (in the code below) by checking if the target
range has more than one column or row (If Target.Columns.Count 1 Or
Target.Rows.Count 1 Then). If that is the case the code then loops through
each column and row and retrieves the value of the cells(col,row). The
sample code shows the change in a messagebox, you will have change the code
to put it in the "log sheet".

As for the second problem, the property "HasFormula" detects if the
particular cell in the target range contains a formula or a value. In the
first case the messagebox returns the ".Formula" property, else the
".Value".

As for the third problem. The "Worksheet_Change" event is only triggered
when something is actually entered into a cell. Not when a cell's value
changes because it contains a formula dependent on the changed cell. You can
check that with the code below . For example put value 1 in A1, 2 in A2 and
formula "=SUM(A1:A2)" in A3. All three entries trigger the Worheets_Change
event and show what has changed. When you now change A1 to another value,
the event only shows that cell A1 was changed, not the value in A3.

The code below, should do the trick:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Col As Long
Dim Row As Long

If Target.Columns.Count 1 Or Target.Rows.Count 1 Then
For Col = Target.Column To Target.Column + Target.Columns.Count - 1
For Row = Target.Row To Target.Row + Target.Rows.Count - 1
If Cells(Row, Col).HasFormula Then
MsgBox ("Row " & Row & " in column " & Col & " has changed
to formula: " & Cells(Row, Col).Formula)
Else
MsgBox ("Row " & Row & " in column " & Col & " has changed
to value: " & Cells(Row, Col).Value)
End If
Next Row
Next Col
Else
If Target.HasFormula Then
MsgBox ("Row " & Target.Row & " in column " & Target.Column & " has
changed to formula: " & Target.Formula)
Else
MsgBox ("Row " & Target.Row & " in column " & Target.Column & " has
changed to value: " & Target.Value)
End If
End If

End Sub



Good luck

"Unexperienced user" wrote in message
...
Dear Anita, I will appreciate if you allow me to can make
a comment on your request.

Dear Binzelli,

It looks it won't work on the following cases:
1. When updating several cells at the same time
(selecting range, and pressing Ctrl+Enter and input the
same value into every selected cell).
2. When writing formulas. The Log will reflect the value
not the formula.
3. When changing any value on a table, formulas will be
updated and hence changes on those (formula) cells will
be reflected on the Log sheet (although they should not
be there since these cells were not actually changed,
only the predecessors).

Is there a way to make work with these restrictions?

Sorry it this is too much :-(

Thanks for your reply.
-----Original Message-----
Dear Binzelli,

Thanks much! This looks like it just may be what I'm
looking for. I'm
going to try it as soon as I get in to work.

Thanks again,
Anita

Binzelli wrote:

Anita,

No problem, use the Worksheet_Change event in the code
for the sheet you
want to "track" (right click on the sheet tab and
select the menu item to
add code to that sheet)


The code below writes the change to a sheet
called "Log"
The row used to write the last change is stored in
cell B1 and is
incremented each time the event fires.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Lastrow As Long
Lastrow = ActiveWorkbook.Sheets("Log").Range
("B1").Value

'Write the Change
ActiveWorkbook.Sheets("Log").Cells(Lastrow + 1, 1)
= "Sheet (name) - Row " &
Target.Row & " - Column " & Target.Column & " changed
to: " & Target.Value

'Increase the LastRow value by 1
ActiveWorkbook.Sheets("Log").Range("B1").Value =
Lastrow + 1

End Sub




Good luck

"a" wrote in message
news:d9gCc.11261
...

Hi,

Is there a way to track changes made to a worksheet
using VBA?

I was thinking that perhaps there was an event
procedure. I envision
that the change could be displayed on a hidden sheet
when a change is
made - similar to the tracking changes tool but
without sharing a

workbook.

I'm on a deadline with this one and so any help would
be greatly
appreciated. I've asked about this issue before and
haven't received
any replies and so I'm thinking that maybe this cannot
be done.

Thanks in advance for any help,
anita






.

Hi !

Your first 'problem' is solved (in the code below) by checking if the target
range has more than one column or row (If Target.Columns.Count 1 Or
Target.Rows.Count 1 Then). If that is the case the code then loops through
each column and row and retrieves the value of the cells(col,row). The
sample code shows the change in a messagebox, you will have change the code
to put it in the "log sheet".

As for the second problem, the property "HasFormula" detects if the
particular cell in the target range contains a formula or a value. In the
first case the messagebox returns the ".Formula" property, else the
".Value".

As for the third problem. The "Worksheet_Change" event is only triggered
when something is actually entered into a cell. Not when a cell's value
changes because it contains a formula dependent on the changed cell. You can
check that with the code below . For example put value 1 in A1, 2 in A2 and
formula "=SUM(A1:A2)" in A3. All three entries trigger the Worheets_Change
event and show what has changed. When you now change A1 to another value,
the event only shows that cell A1 was changed, not the value in A3.

The code below, should do the trick:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Col As Long
Dim Row As Long

If Target.Columns.Count 1 Or Target.Rows.Count 1 Then
For Col = Target.Column To Target.Column + Target.Columns.Count - 1
For Row = Target.Row To Target.Row + Target.Rows.Count - 1
If Cells(Row, Col).HasFormula Then
MsgBox ("Row " & Row & " in column " & Col & " has changed
to formula: " & Cells(Row, Col).Formula)
Else
MsgBox ("Row " & Row & " in column " & Col & " has changed
to value: " & Cells(Row, Col).Value)
End If
Next Row
Next Col
Else
If Target.HasFormula Then
MsgBox ("Row " & Target.Row & " in column " & Target.Column & " has
changed to formula: " & Target.Formula)
Else
MsgBox ("Row " & Target.Row & " in column " & Target.Column & " has
changed to value: " & Target.Value)
End If
End If

End Sub



Good luck




"Unexperienced user" wrote in message
...
Dear Anita, I will appreciate if you allow me to can make
a comment on your request.

Dear Binzelli,

It looks it won't work on the following cases:
1. When updating several cells at the same time
(selecting range, and pressing Ctrl+Enter and input the
same value into every selected cell).
2. When writing formulas. The Log will reflect the value
not the formula.
3. When changing any value on a table, formulas will be
updated and hence changes on those (formula) cells will
be reflected on the Log sheet (although they should not
be there since these cells were not actually changed,
only the predecessors).

Is there a way to make work with these restrictions?

Sorry it this is too much :-(

Thanks for your reply.
-----Original Message-----
Dear Binzelli,

Thanks much! This looks like it just may be what I'm
looking for. I'm
going to try it as soon as I get in to work.

Thanks again,
Anita

Binzelli wrote:

Anita,

No problem, use the Worksheet_Change event in the code
for the sheet you
want to "track" (right click on the sheet tab and
select the menu item to
add code to that sheet)


The code below writes the change to a sheet
called "Log"
The row used to write the last change is stored in
cell B1 and is
incremented each time the event fires.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Lastrow As Long
Lastrow = ActiveWorkbook.Sheets("Log").Range
("B1").Value

'Write the Change
ActiveWorkbook.Sheets("Log").Cells(Lastrow + 1, 1)
= "Sheet (name) - Row " &
Target.Row & " - Column " & Target.Column & " changed
to: " & Target.Value

'Increase the LastRow value by 1
ActiveWorkbook.Sheets("Log").Range("B1").Value =
Lastrow + 1

End Sub




Good luck

"a" wrote in message
news:d9gCc.11261
...

Hi,

Is there a way to track changes made to a worksheet
using VBA?

I was thinking that perhaps there was an event
procedure. I envision
that the change could be displayed on a hidden sheet
when a change is
made - similar to the tracking changes tool but
without sharing a

workbook.

I'm on a deadline with this one and so any help would
be greatly
appreciated. I've asked about this issue before and
haven't received
any replies and so I'm thinking that maybe this cannot
be done.

Thanks in advance for any help,
anita






.

Thanks again, Binzelli!

I'm off to try this at work though, considering that your code did do
exactly what I asked - I just didn't ask enough, I'm sure that this will
do the trick.

Best Regards,
Anita

Binzelli wrote:

Hi !

Your first 'problem' is solved (in the code below) by checking if the target
range has more than one column or row (If Target.Columns.Count 1 Or
Target.Rows.Count 1 Then). If that is the case the code then loops through
each column and row and retrieves the value of the cells(col,row). The
sample code shows the change in a messagebox, you will have change the code
to put it in the "log sheet".

As for the second problem, the property "HasFormula" detects if the
particular cell in the target range contains a formula or a value. In the
first case the messagebox returns the ".Formula" property, else the
".Value".

As for the third problem. The "Worksheet_Change" event is only triggered
when something is actually entered into a cell. Not when a cell's value
changes because it contains a formula dependent on the changed cell. You can
check that with the code below . For example put value 1 in A1, 2 in A2 and
formula "=SUM(A1:A2)" in A3. All three entries trigger the Worheets_Change
event and show what has changed. When you now change A1 to another value,
the event only shows that cell A1 was changed, not the value in A3.

The code below, should do the trick:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Col As Long
Dim Row As Long

If Target.Columns.Count 1 Or Target.Rows.Count 1 Then
For Col = Target.Column To Target.Column + Target.Columns.Count - 1
For Row = Target.Row To Target.Row + Target.Rows.Count - 1
If Cells(Row, Col).HasFormula Then
MsgBox ("Row " & Row & " in column " & Col & " has changed
to formula: " & Cells(Row, Col).Formula)
Else
MsgBox ("Row " & Row & " in column " & Col & " has changed
to value: " & Cells(Row, Col).Value)
End If
Next Row
Next Col
Else
If Target.HasFormula Then
MsgBox ("Row " & Target.Row & " in column " & Target.Column & " has
changed to formula: " & Target.Formula)
Else
MsgBox ("Row " & Target.Row & " in column " & Target.Column & " has
changed to value: " & Target.Value)
End If
End If

End Sub



Good luck




"Unexperienced user" wrote in message
...

Dear Anita, I will appreciate if you allow me to can make
a comment on your request.

Dear Binzelli,

It looks it won't work on the following cases:
1. When updating several cells at the same time
(selecting range, and pressing Ctrl+Enter and input the
same value into every selected cell).
2. When writing formulas. The Log will reflect the value
not the formula.
3. When changing any value on a table, formulas will be
updated and hence changes on those (formula) cells will
be reflected on the Log sheet (although they should not
be there since these cells were not actually changed,
only the predecessors).

Is there a way to make work with these restrictions?

Sorry it this is too much :-(

Thanks for your reply.

-----Original Message-----
Dear Binzelli,

Thanks much! This looks like it just may be what I'm

looking for. I'm

going to try it as soon as I get in to work.

Thanks again,
Anita

Binzelli wrote:


Anita,

No problem, use the Worksheet_Change event in the code

for the sheet you

want to "track" (right click on the sheet tab and

select the menu item to

add code to that sheet)


The code below writes the change to a sheet

called "Log"

The row used to write the last change is stored in

cell B1 and is

incremented each time the event fires.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim Lastrow As Long
Lastrow = ActiveWorkbook.Sheets("Log").Range

("B1").Value

'Write the Change
ActiveWorkbook.Sheets("Log").Cells(Lastrow + 1, 1)

= "Sheet (name) - Row " &

Target.Row & " - Column " & Target.Column & " changed

to: " & Target.Value

'Increase the LastRow value by 1
ActiveWorkbook.Sheets("Log").Range("B1").Val ue =

Lastrow + 1

End Sub




Good luck

"a" wrote in message
news:d9gCc.11261

...

Hi,

Is there a way to track changes made to a worksheet

using VBA?

I was thinking that perhaps there was an event

procedure. I envision

that the change could be displayed on a hidden sheet

when a change is

made - similar to the tracking changes tool but

without sharing a

workbook.


I'm on a deadline with this one and so any help would

be greatly

appreciated. I've asked about this issue before and

haven't received

any replies and so I'm thinking that maybe this cannot

be done.

Thanks in advance for any help,
anita





.