ExcelBanter - Help - desparate for an expanded history log without sharing a workbook

Anita,

Check my answer to a followup of your post:

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" wrote in message
link.net...
I received a reply to my question about Binzelli and it was quite
helpful. Unfortunately, it was limited to changes in cells - not groups
of cells, inserting columns, etc.

Is there anybody out there that can expand on Binzelli procedure? I
changed the log to include "formula" instead of "value" and I added who
made the change using document properties and also added the time that
the change was made. Unfortunately, I left this at work so I don't have
an example.

But, as you can see, the code does what I asked - I just didn't ask for
enough.

Any help would be much appreciated, because, as I said before, I'm on a
really tight deadline and I'm very concerned that I won't be able to
come through with anything worthwhile.

Thanks very much in advance,
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