I always seem to spend ten minutes trying to find these snippets of code on the Internet when I need them. For reference . . .

To trigger a macro on a change in range, load the procedure below into a sheet module. It will fire when a change is made to the range A1:A5.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
        Your Code Here
    End If
End Sub

To trigger a macro on a change in the sheet, load the procedure below into a sheet module. It will fire when a change is made to any cell on the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Your Code Here                
End Sub

A recent post to the Microsoft Excel Charting Discussion Group asks . . . how can I change the point size of all XY chart values that are duplicates? Assuming an embedded XY chart, I responded with the procedure below. Activate (click on) the embedded chart and run the macro:

Sub ShowXYDuplicatePoints()

    Application.ScreenUpdating = False

    Dim Cht As Chart
    Dim Srs As Series
    Dim Pt As Points
    Dim nPts As Long, iPt As Long
    Dim Test As Variant
    Dim UniqueValues As New Collection

    Set Cht = ActiveChart

    For Each Srs In Cht.SeriesCollection
        With Srs
            nPts = .Points.Count
            For iPt = 1 To nPts
                Test = Srs.XValues(iPt) & Srs.Values(iPt)
                UniqueValues.Add Acct, CStr(Test)
                On Error GoTo ErrHandler:
                If iPt + 1 > nPts Then
                    ActiveChart.Deselect
                    Exit Sub
                End If
                Label1:
            Next
        End With
    Next Srs

ErrHandler:
    Srs.Points(iPt).MarkerSize = 10
    Srs.Points(iPt).MarkerBackgroundColorIndex = 3
    Srs.Points(iPt).MarkerForegroundColorIndex = 3
    If iPt + 1 > nPts Then
        ActiveChart.Deselect
        Exit Sub
    End If
    Resume Label1:

End Sub

The procedure works by looping through all of the point values within each series in the chart. As the macro loops, each point value is loaded into the UniqueValues array. Because the UniqueValues array only accepts unique values, any duplicates are automatically kicked out and run through the error handling procedure.

The formatting can be reset with the following procedure.

Sub ResetXY()

    Application.ScreenUpdating = False

    Dim Cht As Chart
    Dim Srs As Series
    Dim Pt As Points
    Dim nPts As Long, iPt As Long
    Set Cht = ActiveChart

    For Each Srs In Cht.SeriesCollection
        With Srs
            nPts = .Points.Count
            For iPt = 1 To nPts
                Srs.Points(iPt).MarkerSize = 5
                Srs.Points(iPt).MarkerBackgroundColorIndex = 11
                Srs.Points(iPt).MarkerForegroundColorIndex = 11
            Next
        End With
    Next Srs

    ActiveChart.Deselect

End Sub

A recent post to the Microsoft Excel Charting Discussion Group asks . . . how can I change the pattern of a column based on that column's value. Assuming an embedded chart, I responded with the following procedure:

Sub ChangePatterns()

    Application.ScreenUpdating = False

    Dim Cht As Chart
    Dim Srs As Series
    Dim Pts As Points

    Set Cht = ActiveChart
    Set Srs = Cht.SeriesCollection(1)
    Set Pts = Srs.Points

    Cnt = 1

    For Each Pt In Srs.Values

        Srs.Points(Cnt).Select

        'Sales greater than 10000
        If Pt > 10000 Then

            With Selection
                .Fill.Visible = True
                .Fill.Patterned Pattern:=msoPatternWideUpwardDiagonal
                .Fill.ForeColor.SchemeColor = 42
                .Fill.BackColor.SchemeColor = 34
            End With

        'Sales less than or equal to 10000
        ElseIf Pt <= 10000 Then

            With Selection
                .Fill.Visible = True
                .Fill.Patterned Pattern:=msoPatternLightHorizontal
                .Fill.ForeColor.SchemeColor = 43
                .Fill.BackColor.SchemeColor = 22
            End With

        End If

        Cnt = Cnt + 1

    Next Pt

    ActiveChart.Deselect

End Sub

-- Just thought I would document it for my own reference. Is there a better way to write this?

Microsoft's macrofun.hlp file documents the XLM function GET.CHART.ITEM, which is useful for finding the vertical and horizontal positions for selected chart items. This function is further explained in Professional Excel Development.

From Microsoft's macrofun.hlp, below is the documentation for GET.CHART.ITEM:

GET.CHART.ITEM returns the vertical or horizontal position of a point on a chart item. Use these position numbers with FORMAT.MOVE and FORMAT.SIZE to change the position and size of chart items. Position is measured in points; a point is 1/72nd of an inch.

Syntax

GET.CHART.ITEM(x_y_index, point_index, item_text)

X_Y_index is a number specifying which of the coordinates you want returned.

X_Y_index Coordinate returned:

• 1 - Horizontal coordinate.
• 2 - Vertical coordinate.

Point_index is a number specifying the point on the chart item. These indexes are described later. If point_index is omitted, it is assumed to be 1. If the specified item is a point, point_index must be 1. If the specified item is any line other than a data line, use the following values for point_index.

Point_index and Chart item position:

• 1 - Lower or left.
• 2 - Upper or right.

If the selected item is a legend, plot area, chart area, or an area in an area chart, use the following values for point_index.

Point_index and Chart item position:

• 1 - Upper left.
• 2 - Upper middle.
• 3 - Upper right.
• 4 - Right middle.
• 5 - Lower right.
• 6 - Lower middle.
• 7 - Lower left.
• 8 - Left middle.

If the selected item is an arrow in Microsoft Excel 4.0, use the following values for point_index. In Microsoft Excel 5.0, arrows are named lines, and the arrowhead position returned is equivalent to the end of a line where the arrowhead begins.

Point_index and Chart item position:

• 1 - Arrow shaft.
• 2 - Arrowhead.

If the selected item is a pie slice, use the following values for point_index.

Point_index and Chart item position:

• 1 - Outermost counterclockwise point.
• 2 - Outer center point.
• 3 - Outermost clockwise point.
• 4 - Midpoint of the most clockwise radius.
• 5 - Center point.
• 6 - Midpoint of the most counterclockwise radius.

Item_text is a selection code that specifies which item of a chart to select. See the chart form of SELECT for the item_text codes to use for each item of a chart.

If item_text is omitted, it is assumed to be the currently selected item.

If item_text is omitted and no item is selected, GET.CHART.ITEM returns the #VALUE! error value.

Remarks

If the specified item does not exist, or if a chart is not active when the function is carried out, the #VALUE! error value is returned.

Examples

The following macro formulas return the horizontal and vertical locations, respectively, of the top of the main-chart value axis:

GET.CHART.ITEM(1, 2, "Axis 1")

GET.CHART.ITEM(2, 2, "Axis 1")

You could then use FORMAT.MOVE to move a floating text item to the position returned by these two formulas.

In the final post of this series, I've completed the first version of the formula trace tool by adding the following:

• The option to trace dependent cells.
• A "Step" function that allows the user to move through precedent or dependent cell references. It reactivates the tool from any precedent or dependent cell reference.
• A "Clear" function that allows the user to clear the audit arrows without closing the tool.
• A command button to activate the tool. The command button symbol is a blue arrow pointing to the right over a white background.

The first version of the tool looks like this:

The tool can be used as a regular spreadsheet file (.xls) or as an add-in (.xla). To use as a spreadsheet file, open TraceTool.xls as you would any other spreadsheet. The command button with the blue arrow should appear. Then open the spreadsheet that you want to audit. To use the tool as an addin, open TraceTool.xls and then save it as TraceTool.xla.

You can download the utility via the link below:

To activate the tool, click on the command button:

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.