Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tracking Changes in a non-shared workbook
Is there any macro that could track changes in an Excel workbook without the
workbook being shared? I came across the following macro: Dim nFile nFile = FreeFile Open "C:\MyLog.txt" For Append As #nFile Print #nFile, "Workbook " & ThisWorkbook.Path & _ " opened by " & Environ("UserName") & _ " on " & Format(Now, "yyyy/mm/dd hh:mm:ss") Close #nFile which saves a list of when the file is accessed. But I've no idea how to amend this to list any changes to the the workbook. In actual fact, it's only one specific sheet that I need to track, however the nature of the file won't allow me to share it. Any help would be much appreciated. Andrew. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tracking Changes in a non-shared workbook
Maybe you could tie into the worksheet_change event and add some more text (each
cell being changed--it's address and new value) and put them into your text file, too. But remember that if the user closes the workbook without saving, your text file with those changes doesn't make sense. It might make more sense to keep track of the changes in (multiple??) worksheets stored within the same workbook. Something like this may give you a start if you want to do that. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim DestCell As Range Dim LogWks As Worksheet Dim myCell As Range Set LogWks = Worksheets("logsheet") If Intersect(Target, Me.UsedRange) Is Nothing Then Exit Sub End If With LogWks Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With For Each myCell In Intersect(Target, Me.UsedRange).Cells DestCell.Value = Application.UserName With DestCell.Offset(0, 1) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = Now End With DestCell.Offset(0, 2).Value = myCell.Address(0, 0) With DestCell.Offset(0, 3) .NumberFormat = "@" .Value = myCell.Value End With With DestCell.Offset(0, 4) .NumberFormat = "@" .Value = myCell.Formula End With Set DestCell = DestCell.Offset(1, 0) Next myCell End Sub Andrew wrote: Is there any macro that could track changes in an Excel workbook without the workbook being shared? I came across the following macro: Dim nFile nFile = FreeFile Open "C:\MyLog.txt" For Append As #nFile Print #nFile, "Workbook " & ThisWorkbook.Path & _ " opened by " & Environ("UserName") & _ " on " & Format(Now, "yyyy/mm/dd hh:mm:ss") Close #nFile which saves a list of when the file is accessed. But I've no idea how to amend this to list any changes to the the workbook. In actual fact, it's only one specific sheet that I need to track, however the nature of the file won't allow me to share it. Any help would be much appreciated. Andrew. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
shared workbook - shared workbook options grayed out | Excel Discussion (Misc queries) | |||
When tracking changes in excel shared workbooks, do all users see | Excel Discussion (Misc queries) | |||
When tracking changes in excel shared workbooks, do all users see | New Users to Excel | |||
Tracking Changes in Shared Worked Book | Excel Worksheet Functions | |||
Tracking Instantaneously When the Shared Excel Workbook Is Opened. | Excel Discussion (Misc queries) |