Tracking old & new formula changes & other changes w/ links to original cells
Hello all,
I'm currently implementing a process to track changes in an Excel workbook due to version control issues. Shown below is the complete text of the VBA macros that I've started in "ThisWorkbook" object in an Excel 2007 workbook. I'm a novice when it comes to VBA codes, so your help is greatly appreciated.
1. I need to run tracked changes for all spreadsheets within the workbook that have tab names partially matched to "Fiscal Year 201X." How can I direct the Tracker log to identify any tracked changes in tabs with similar tab names?
2. How do I show the old and new formulas in the "Tracker" tab? For example, tab "Fiscal Year 2014" Cell A4's original formula is A1+A2. However, the new formula is A1+A2+A3. How do these formulas appear on the "Tracker" tab as =A1+A2 and =A1+A2+A3 for old and new, respectively?
3. Is there a way to add hyperlinks to the columns labeled "Cell Changed" and "New Value" that point to their respective cells? Based on the example above, how can the Tracker hyperlink to the cell in "Cell Changed" and "New Value" columns to reflect "='Fiscal Year 2014'!C4"? I don't want to see the values being hard-coded and rather they should be linked directly to that other spreadsheet so people can click on them easily and quickly.
4. When itemizing the type of changes, is it possible to add an "Index" column that enumerates the changes in sequential order? If yes, how?
5. If people insert/delete rows, will that be tracked? If yes, how?
6. When people insert comments or textboxes to any "Fiscal Year 2" tab, will those be tracked as well? If yes, how?
7. I would like to have the Tracker's column headers (e.g., Cell Changed, New Value) to be bolded and have a bottom border every time updates are made to the Tracker. Can you show me how to do this?
Thank you so much for your help!!
- Minh
Code:
Option Explicit
Dim vOldVal 'Must be at top of module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim bBold As Boolean
If Target.Cells.Count > 1 Then Exit Sub
'On Error Resume Next
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
bBold = Target.HasFormula
With Sheets("Tracker")
.Unprotect Password:="Secret"
If .Range("A1") = vbNullString Then
.Range("A1:H1") = Array("Cell Changed", "Old Value", _
"New Value", "Old Formula", "New Formula", "Time of Change", "Date of Change", "User")
.Range.Bold = bBold
End If
With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
.Value = ActiveSheet.Name & " : " & Target.Address
.Offset(0, 1) = vOldVal
With .Offset(0, 2)
If bBold = True Then
.ClearComments
End If
.Value = Target
.Font.Bold = bBold
End With
.Offset(0, 5) = Time
.Offset(0, 6) = Date
.Offset(0, 7) = Application.UserName
End With
.Cells.Columns.AutoFit
.Protect Password:="Secret"
End With
vOldVal = vbNullString
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
On Error GoTo 0
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
vOldVal = Target
End Sub
Private Sub test()
Application.EnableEvents = True
End Sub
