As a recap to Building the Formula Trace Tool Post 1, a macro was created that records the cell address and worksheet name for each precedent cell based on the active cell address. Each precedent cell address and worksheet name are then converted into a test string and then loaded into an array.

The next steps are to load the results of the array into a listbox. To do so, first I created a userform that contains a listbox (shown below in the design view). The userform is called "frmTrace" and the listbox within that userform is called "Listbox1". I've also added a command button (Exit) to right side bottom of the userform called "btnExit".

After creating the userform, I added the following code to the code module to initialize it.

Private Sub UserForm_Initialize()

    Application.ScreenUpdating = False

    frmTrace.StartUpPosition = Manual
    frmTrace.Top = 50
    frmTrace.Left = 500

    Call TracePrecedents

    Application.ScreenUpdating = True

End Sub

To show the userform I added a command button to the worksheet. That command button is named "CB1" and captioned "Trace Formulas". I added the following code to the command button to show the userform.

Private Sub CB1_Click()
    frmTrace.Show
End Sub

When the userform is initialized, it calls a procedure called "TracePrecedents". The TracePrecedents procedure is what was originally presented in Building the Formula Auditing Tool Post 1. After some more work, that procedure has been modified to the one below and added to the "frmTrace" code module.

Private Sub TracePrecedents()

    On Error Resume Next

    Dim iAddress As String
    Dim UniqueValues As New Collection
    Dim iArrow As Integer

    Set Origin = ActiveCell

    Origin.ShowPrecedents

    For iArrow = 1 To 200

        For iLink = 1 To 200

            Err = 0

            Set oPrec = Origin.NavigateArrow(True, iArrow, iLink)

            Cell = oPrec.Address
            Sheet = oPrec.Parent.Name
            Workbook = oPrec.Parent.Parent.Name
            iAddress = "[" & Workbook & "]" & Sheet & "!" & Cell
            UniqueValues.Add iAddress, CStr(iAddress)

            If Err <> 0 Then Exit For

        Next

    Next

    frmTrace.ListBox1.RowSource = ""
    For Each Item In UniqueValues
        frmTrace.ListBox1.AddItem Item
    Next Item

End Sub

At this point the cell references will load into the listbox. However, code still needs to be added that programs the listbox to respond when a cell reference is activated. The procedure below parses the text string into a workbook, worksheet, and cell reference component. Each time the list box is clicked, the event fires.

Private Sub ListBox1_Click()

    Dim iSeg1, iSeg2 As Integer
    Dim iCell, iSheet, iWorkbook As String

    Item = ListBox1.Value

    iSeg1 = Application.WorksheetFunction.Find("]", Item)
    For i = 1 To Len(Item)
        iWorkbook = Mid(Item, 2, iSeg1 - 2)
    Next i

    iSeg2 = Application.WorksheetFunction.Find("!", Item)
    For i = 1 To Len(Item)
        iCell = Mid(Item, iSeg2 + 1, i)
    Next i

    For i = 1 To Len(Item)
        iSheet = Mid(Item, Len(iWorkbook) + 3, Len(Item) _
        - Len(iWorkbook) - Len(iCell) - 3)
    Next i

    Workbooks(iWorkbook).Activate
    Worksheets(iSheet).Select
    Range(iCell).Select

End Sub

In the screenshot below, I've activated the userform by hitting the Trace Formulas button. The active cell is C21 as indicated by the blue background for the cell reference "[TraceTool.xls]Sheet1!$C$21" in the userform. The precedent references for cell C21 are indicated by the blue tracer arrows and are loaded as a list in the userform. As I click on each cell reference in the userform, that cell reference will be selected via the click event shown above.

Finally, I've added code to exit the userform and clear the auditing arrows. The procedure below fires when the Exit button is clicked.

Private Sub btnExit_Click()
    ActiveSheet.ClearArrows
    Unload Me
End Sub

The procedure below fires if the userform is terminated in a way other than using the Exit button.

Private Sub UserForm_Terminate()
    ActiveSheet.ClearArrows
End Sub

In the next post I plan to add code to trace dependent cells and possibly add a feature that allows the user to step through formulas.