Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trach Changes Without sharing the workbook
Hi All,
I'm looking for a way to track changes to worksheets without using sharing the workbook. The information I would like to track is.. Users (Say last 20) Date and Time What has been changed (Added or Deleted) Is this possible? Any help or advice would be appreciated, Thanks in advance, Gav. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trach Changes Without sharing the workbook
It's possible, using Event macros, but it's anything but simple if you
want to accurately keep track of changes (and it depends on the user enabling macros). Is there a specific reason you don't want to share the workbook? In article , Gav123 wrote: Hi All, I'm looking for a way to track changes to worksheets without using sharing the workbook. The information I would like to track is.. Users (Say last 20) Date and Time What has been changed (Added or Deleted) Is this possible? Any help or advice would be appreciated, Thanks in advance, Gav. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trach Changes Without sharing the workbook
I've spent some time on this topic recently and now have a solution with
which I'm satisfied. [I'll plan to share it once I have it cleaned up.] Before I wrote mine, I came across several other solutions. The one at the link below is pretty good. Its limitations are (1) it handles changes to only one cell, if the user changes multiple cells at once, it does not log that change and (2) it does not track formula or formatting changes, only changes to the "value" field. But I believe it addresses your stated need. http://www.ozgrid.com/VBA/track-changes.htm "Gav123" wrote: Hi All, I'm looking for a way to track changes to worksheets without using sharing the workbook. The information I would like to track is.. Users (Say last 20) Date and Time What has been changed (Added or Deleted) Is this possible? Any help or advice would be appreciated, Thanks in advance, Gav. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trach Changes Without sharing the workbook
I've managed to find this solution, which tracks multiple cell changes. I'm
not too worried about formatting or formula changes.. Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("$A$1:$AA$1000")) Is Nothing Then Application.EnableEvents = False Application.ScreenUpdating = False With Worksheets("Sheet2") .Select .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select ActiveCell.Value = Target.Address ActiveCell.Offset(0, 1).Select ActiveCell.Value = Target.Value ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() ActiveCell.NumberFormat = "dd/mm/yy" ActiveCell.Offset(0, 1).Select ActiveCell.Value = Application.UserName Worksheets("sheet1").Select Application.EnableEvents = True Application.ScreenUpdating = True End With End If End Sub What I would like to do now is log the sheet name too as there is multiple named sheets within the workbook, any ideas? Thanks in advance, Gav. "bstobart" wrote: I've spent some time on this topic recently and now have a solution with which I'm satisfied. [I'll plan to share it once I have it cleaned up.] Before I wrote mine, I came across several other solutions. The one at the link below is pretty good. Its limitations are (1) it handles changes to only one cell, if the user changes multiple cells at once, it does not log that change and (2) it does not track formula or formatting changes, only changes to the "value" field. But I believe it addresses your stated need. http://www.ozgrid.com/VBA/track-changes.htm "Gav123" wrote: Hi All, I'm looking for a way to track changes to worksheets without using sharing the workbook. The information I would like to track is.. Users (Say last 20) Date and Time What has been changed (Added or Deleted) Is this possible? Any help or advice would be appreciated, Thanks in advance, Gav. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trach Changes Without sharing the workbook
Have a look at the link I included above. The macro there does what you
need. I think you may have misunderstood me (or I wasn't clear). The macro I referred you to will track changes to all cells in the workbook, but it only logs changes made to one cell at a time. In other words, if you were to copy/paste a range or more than one cell, it would not log that change. Actually, it looks like I made a few minor changes (for instance, adding the worksheet name to the log), so try the version below. I haven't played with this for a while, but it seems to still work. This code is from www.ozgrid.com (see link in my other posting), with only minor changes by me. Note that this macro puts the log into "Sheet1" and hides it, so make sure your "Sheet1" is available to be overwritten, or update the references to Sheet1 below with another sheet. You can unhide it with Format-Sheet-Unhide. Also once you have it working, try copying and pasting. You may find the resetting of the Copymode irritating, but it should be tolerable. You may find the original version preferable since it doesn't have this issue. [You'll need to add back in the logging of the sheetname.] My version shows the value with formatting of the new cell, but only the unformatted value of the old cell. Put the following code in the Thisworkbook module: ------------------------- Option Explicit Dim vOldVal 'Must be at top of module Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With Call LogChanges(vOldVal, Target, Sh) With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) vOldVal = Target End Sub ------------------------------------ Put the following code in a new module (I call mine ChangeLog). -------------------------------------- Option Explicit Sub LogChanges(ByVal vOldVal, ByVal Target As Range, ByVal Sh As Object) Dim bHasFormula As Boolean If Target.Cells.count 1 Then Exit Sub 'On Error Resume Next If IsEmpty(vOldVal) Then vOldVal = "[Empty Cell]" bHasFormula = Target.HasFormula With Sheet1 .Unprotect Password:="Secret" If .Range("A1") = vbNullString Then .Range("A1:H1") = Array("#", "CELL CHANGED", "NUMCELLS", "OLD VALUE", _ "NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE", "USERID") End If With .Cells(.Rows.count, 1).End(xlUp)(2, 1) .Formula = "=Row()-1" .Offset(0, 1) = Target.Worksheet.Name & "!" & Target.Address .Offset(0, 2) = Target.Cells.count .Offset(0, 3) = vOldVal With .Offset(0, 4) If bHasFormula = True Then .Formula = "'" & .Formula Target.Copy .PasteSpecial (xlPasteFormats) Else Target.Copy .PasteSpecial (xlPasteAll) End If End With .Offset(0, 5) = Time .Offset(0, 6) = Date .Offset(0, 7) = Application.UserName End With .Cells.Columns.AutoFit .Protect Password:="Secret" .Visible = xlSheetHidden End With vOldVal = vbNullString On Error GoTo 0 End Sub ---------------------------------- "Gav123" wrote: I've managed to find this solution, which tracks multiple cell changes. I'm not too worried about formatting or formula changes.. Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("$A$1:$AA$1000")) Is Nothing Then Application.EnableEvents = False Application.ScreenUpdating = False With Worksheets("Sheet2") .Select .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select ActiveCell.Value = Target.Address ActiveCell.Offset(0, 1).Select ActiveCell.Value = Target.Value ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() ActiveCell.NumberFormat = "dd/mm/yy" ActiveCell.Offset(0, 1).Select ActiveCell.Value = Application.UserName Worksheets("sheet1").Select Application.EnableEvents = True Application.ScreenUpdating = True End With End If End Sub What I would like to do now is log the sheet name too as there is multiple named sheets within the workbook, any ideas? Thanks in advance, Gav. "bstobart" wrote: I've spent some time on this topic recently and now have a solution with which I'm satisfied. [I'll plan to share it once I have it cleaned up.] Before I wrote mine, I came across several other solutions. The one at the link below is pretty good. Its limitations are (1) it handles changes to only one cell, if the user changes multiple cells at once, it does not log that change and (2) it does not track formula or formatting changes, only changes to the "value" field. But I believe it addresses your stated need. http://www.ozgrid.com/VBA/track-changes.htm "Gav123" wrote: Hi All, I'm looking for a way to track changes to worksheets without using sharing the workbook. The information I would like to track is.. Users (Say last 20) Date and Time What has been changed (Added or Deleted) Is this possible? Any help or advice would be appreciated, Thanks in advance, Gav. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SHARING WORKBOOK | Excel Worksheet Functions | |||
Sharing a workbook off 1 PC?? | Excel Discussion (Misc queries) | |||
Sharing a workbook | Excel Programming | |||
Workbook Sharing | Excel Worksheet Functions | |||
Workbook Sharing | Excel Programming |