The trick here is knowing that when target.count > 1, then the Target Range
becomes an array.
So, to get the cell address and values, you have to loop through the array..
like:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim inx
Dim strFileName As String
strFileName = "c:\temp\file_name.txt" 'Change as appropriate
Open strFileName For Append As #1
If Target.Count > 1 Then
For inx = 0 To Target.Count - 1
Print #1, ActiveWorkbook.Name _
& vbTab _
& Sh.Name _
& vbTab _
& Target(inx).Address _
& vbTab _
& Environ("username") _
& vbTab _
& Date _
& vbTab _
& Time _
& vbTab _
& Target(inx).Value
Next inx
Else
Print #1, ActiveWorkbook.Name _
& vbTab _
& Sh.Name _
& vbTab _
& Target.Address _
& vbTab _
& Environ("username") _
& vbTab _
& Date _
& vbTab _
& Time _
& vbTab _
& Target.Value
End If
Close #1
End Sub
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
________________________________
From: Seba <sebastjan.hri...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Thu, September 29, 2011 4:18:58 AM
Subject: $$Excel-Macros$$ Audit Trail
Hi all,
I need to setup a Audi Trail system for my excel workbooks and I found
this code on the net by google search.
-------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim strFileName As String
strFileName = "Location\file_name.txt" 'Change as appropriate
Open strFileName For Append As #1
If Target.Count > 1 Then
Print #1, ActiveWorkbook.Name & vbTab & Sh.Name & vbTab &
Target.Address & vbTab & Environ("username") & vbTab & Date & vbTab &
Time & vbTab & "Multiple cells changed"
Else
Print #1, ActiveWorkbook.Name & vbTab & Sh.Name & vbTab &
Target.Address & vbTab & Environ("username") & vbTab & Date & vbTab &
Time & vbTab & Target.Value
End If
Close #1
End Sub
----------------------------------------------------
This does the job, but I would like to have all values logged when
multiple cells are changed.
Lets say I paste a range. In the log file it just states "Multiple
cells changed". I tried modifying it, however with no success.
Can anyone please help?
Thank you.
Best regards,
seba
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
