Using VBA to track changes in a worksheet - help! Event procedure??

[a]

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

 

[Binzelli]

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

 

[Binzelli]

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]

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

 

[Unexperienced user]

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.

 

[Fred]

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

 

[Binzelli]

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

 

[a]

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