Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Track Changes on Multi-Cell Targets?
In my case, I can't use Share Workbook/Track changes so I created my own
macro to track change history per www.ozgrid.com/VBA/track-changes.htm and other sources. The problem is that I can't find anything that will help me track when multiple cells are the "Target". A good example is if someone grabs a range of 10 cells and hit's "Delete". My code only looks at single cell targets. Excel's change-tracking allows that functionality but can't find any place that explains how to do it. Thanks! MikeZz Private Sub Worksheet_Change(ByVal Target As Range) Dim bBold As Boolean Dim thisHead Dim rngHist ' 'Code taken from he 'http://www.ozgrid.com/VBA/track-changes.htm ' If SheetVeryHidden.Range("ActivaterChangeTracking").V alue < True Then Exit Sub End If cellcolGUID = Range("GUID").Column cellcolToday = Range("colToday").Column cellcolHist = Range("colHistory").Column cellcolSQ = Range("colCellSQ").Column If Target.Cells.Count 1 Or Target.Column = cellcolSQ Then Exit Sub On Error Resume Next If vOldVal = Target Then Exit Sub With Application .ScreenUpdating = False .EnableEvents = False End With If IsEmpty(vOldVal) Then vOldVal = "Empty Cell" bBold = Target.HasFormula changeCount = changeCount + 1 arrChanges(changeCount, colAdd) = Target.Address 'HeadingRow thisHead = Cells(HeadingRow, Target.Column) arrChanges(changeCount, colHed) = thisHead arrChanges(changeCount, colGUID) = Cells(Target.Row, cellcolGUID) Set rngHist = Cells(Target.Row, cellcolHist) editTime = Format(Now(), "mmm dd h:m:s") & "] " rngHist.Value = "[" & strUserInit & "! " & editTime & thisHead & ": " & vOldVal & " " & Target & Chr(10) & rngHist.Value Set rngHist = Nothing arrChanges(changeCount, colOld) = vOldVal If bBold = True Then arrChanges(changeCount, colNew) = "'" & Target.Formula & "=" & Target Else arrChanges(changeCount, colNew) = Target End If arrChanges(changeCount, colTim) = Time arrChanges(changeCount, colDat) = Date arrChanges(changeCount, colUsr) = strUser GoTo skipWith With Changes ' .Unprotect Password:="Secret" If .Range("A1") = vbNullString Then .Range("A1:E1") = Array("CELL CHANGED", "OLD VALUE", _ "NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE") End If With .Cells(.rows.Count, 1).End(xlUp)(2, 1) .Value = Target.Address .Offset(0, 1) = vOldVal With .Offset(0, 2) If bBold = True Then .ClearComments .AddComment.Text Text:= _ "OzGrid.com:" & Chr(10) & "" & Chr(10) & _ "Bold values are the results of formulas" End If .Value = Target .Font.Bold = bBold End With .Offset(0, 3) = Time .Offset(0, 4) = Date .Offset(0, 5) = strUser End With .Cells.Columns.AutoFit ' .Protect Password:="Secret" End With skipWith: vOldVal = vbNullString With Application .ScreenUpdating = True .EnableEvents = True End With On Error GoTo 0 End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.HasFormula = True Then vOldVal = "'" & Target.Formula & "=" & Target Else vOldVal = Target End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Track Changes on Multi-Cell Targets?
Target is just the selected range when the change is made, so to deal
with all selected cells (including multiple areas): Dim rArea As Range Dim rCell As Range For Each rArea In Target.Areas For Each rCell In rArea 'your individual cell code here Next rCell Next rArea Of course, one can add a layer of complexity, too, if multiple worksheets are selected: Dim ws As Worksheet Dim rArea As Range Dim rCell As Range For Each ws in ActiveWindow.SelectedSheets For Each rArea in ws.Range(Target.Address).Areas For Each rCell In rArea 'individual cell code Next rCell Next rArea Next ws In article , MikeZz wrote: In my case, I can't use Share Workbook/Track changes so I created my own macro to track change history per www.ozgrid.com/VBA/track-changes.htm and other sources. The problem is that I can't find anything that will help me track when multiple cells are the "Target". A good example is if someone grabs a range of 10 cells and hit's "Delete". My code only looks at single cell targets. Excel's change-tracking allows that functionality but can't find any place that explains how to do it. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Track Changes on Multi-Cell Targets?
Have you tried:
If(IsArray(Target)) 'blah else 'blah end if In the worksheet_change or worksheet_selectionchange events? -- HTH Roger Shaftesbury (UK) (Excel 2003, Win XP/SP2) "MikeZz" wrote in message ... In my case, I can't use Share Workbook/Track changes so I created my own macro to track change history per www.ozgrid.com/VBA/track-changes.htm and other sources. The problem is that I can't find anything that will help me track when multiple cells are the "Target". A good example is if someone grabs a range of 10 cells and hit's "Delete". My code only looks at single cell targets. Excel's change-tracking allows that functionality but can't find any place that explains how to do it. Thanks! MikeZz Private Sub Worksheet_Change(ByVal Target As Range) Dim bBold As Boolean Dim thisHead Dim rngHist ' 'Code taken from he 'http://www.ozgrid.com/VBA/track-changes.htm ' If SheetVeryHidden.Range("ActivaterChangeTracking").V alue < True Then Exit Sub End If cellcolGUID = Range("GUID").Column cellcolToday = Range("colToday").Column cellcolHist = Range("colHistory").Column cellcolSQ = Range("colCellSQ").Column If Target.Cells.Count 1 Or Target.Column = cellcolSQ Then Exit Sub On Error Resume Next If vOldVal = Target Then Exit Sub With Application .ScreenUpdating = False .EnableEvents = False End With If IsEmpty(vOldVal) Then vOldVal = "Empty Cell" bBold = Target.HasFormula changeCount = changeCount + 1 arrChanges(changeCount, colAdd) = Target.Address 'HeadingRow thisHead = Cells(HeadingRow, Target.Column) arrChanges(changeCount, colHed) = thisHead arrChanges(changeCount, colGUID) = Cells(Target.Row, cellcolGUID) Set rngHist = Cells(Target.Row, cellcolHist) editTime = Format(Now(), "mmm dd h:m:s") & "] " rngHist.Value = "[" & strUserInit & "! " & editTime & thisHead & ": " & vOldVal & " " & Target & Chr(10) & rngHist.Value Set rngHist = Nothing arrChanges(changeCount, colOld) = vOldVal If bBold = True Then arrChanges(changeCount, colNew) = "'" & Target.Formula & "=" & Target Else arrChanges(changeCount, colNew) = Target End If arrChanges(changeCount, colTim) = Time arrChanges(changeCount, colDat) = Date arrChanges(changeCount, colUsr) = strUser GoTo skipWith With Changes ' .Unprotect Password:="Secret" If .Range("A1") = vbNullString Then .Range("A1:E1") = Array("CELL CHANGED", "OLD VALUE", _ "NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE") End If With .Cells(.rows.Count, 1).End(xlUp)(2, 1) .Value = Target.Address .Offset(0, 1) = vOldVal With .Offset(0, 2) If bBold = True Then .ClearComments .AddComment.Text Text:= _ "OzGrid.com:" & Chr(10) & "" & Chr(10) & _ "Bold values are the results of formulas" End If .Value = Target .Font.Bold = bBold End With .Offset(0, 3) = Time .Offset(0, 4) = Date .Offset(0, 5) = strUser End With .Cells.Columns.AutoFit ' .Protect Password:="Secret" End With skipWith: vOldVal = vbNullString With Application .ScreenUpdating = True .EnableEvents = True End With On Error GoTo 0 End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.HasFormula = True Then vOldVal = "'" & Target.Formula & "=" & Target Else vOldVal = Target End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Track Changes on Multi-Cell Targets?
Hmmm...
Select A1, C3, and E5. Enter a value in one of the cells. See which "blah" gets executed... In article , "Roger Whitehead" wrote: Have you tried: If(IsArray(Target)) 'blah else 'blah end if In the worksheet_change or worksheet_selectionchange events? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Track Changes on Multi-Cell Targets?
Yep, I didn't read properly - sorry...
Roger "JE McGimpsey" wrote in message ... Hmmm... Select A1, C3, and E5. Enter a value in one of the cells. See which "blah" gets executed... In article , "Roger Whitehead" wrote: Have you tried: If(IsArray(Target)) 'blah else 'blah end if In the worksheet_change or worksheet_selectionchange events? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I Track Changes on Multi-Cell Targets?
Thanks for the tips.
I'll give it a try and post how it goes. "Roger Whitehead" wrote: Yep, I didn't read properly - sorry... Roger "JE McGimpsey" wrote in message ... Hmmm... Select A1, C3, and E5. Enter a value in one of the cells. See which "blah" gets executed... In article , "Roger Whitehead" wrote: Have you tried: If(IsArray(Target)) 'blah else 'blah end if In the worksheet_change or worksheet_selectionchange events? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Targets on PivotChart | Charts and Charting in Excel | |||
passing targets between functions | Excel Programming | |||
Sum To Work Out Targets | Excel Discussion (Misc queries) | |||
Delete Select Targets | Excel Discussion (Misc queries) | |||
how can I paste multi-line/multi-paragraph data into ONE cell? | Excel Discussion (Misc queries) |