Tracking old & new formula changes & other changes w/ links to original cells

[enigmadreama]

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

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

[westconn1]

1. try

if sh.name like "Fiscal Year 2*" then
'OR
if instr(sh.name, "Fiscal Year 2") > 0 then

how many version changes do you want to track?

you are going to have to test for yourself items 5 and 6
also, how you handle if target is muliple cells, rather than a single cell
that is if someone selects an area an presses delete or similar