Hiding source data

["Walter Lake" <wlake@xxxxxxxxx>]

Hi Lars and others

Yes, I trade commodity spreads. It's not uncommon to have 30 years of
historical spread trades overlayed on an Excel chart. Although, I normally
sort them into 4 charts: long winners & losers and short winners and losers
for further analysis.

I've also seen this multi price plot technique used on charts of stocks in a
sector, mutual funds in a sector and with front and back months of futures
contracts.

Even though this is a great feature of Excel, charts can get very crowded. I
use the following techniques published at John Walkenbach' site to
hide/unhide one or more columns of price data on the source worksheet and
thus hide/unhide the price plot on the chart.

The enclosed VBA code is for listboxes to put on the chart to avoid
switching continually between the source worksheet and the chart.

Best regards

Walter

=============================
http://www.j-walk.com/ss/excel/eee/eee010.txt
By David Hager

There is a little-known effect for the display of charted data that can add
considerable polish to a chart presentation. This applies to data ordered
by either rows or columns. When records are arranged by rows, they can be
hidden through filtering by using Data, Filter, AutoFilter. Columns of
charted data can be hidden by using the Format, Column, Hide command. In
either case, it turns out that the data that has been filtered or hidden
no longer appears on the chart. This effect is quite useful for the
viewing of data with a single chart, since what appears on the chart is
controlled by the visible data on the worksheet. For example, you can have
a chart with many data series and view them one at a time. Other descriptive
fields or rows can be added to the data table that enhance to ability to
filter the data in different ways. Due to the options available for the
manipulation of data in the data filtering process, this effect works best
when the data is ordered in rows, assuming that the data set is not larger
than the number of columns.

One drawback to using this technique for the display on information in a
meeting is that changes to the source data would have to be done by toggling
between the chart and worksheet holding the data. Fortunately, there is an
easy way to overcome this problem. The desired filter settings can be stored
in custom views. Then, a listbox with those views can be added to the chart
sheet. Since an ActiveX listbox cannot be used on a chart sheet, you will
have
to use the native Excel listbox that is available from the Forms toolbar.
You
can add the desired custom view names programatically or by linking it to a
worksheet range. The following procedure will add all of the custom views in
a workbook to the listbox. It contains a workaround for a problem in Excel
that
prevents a normal looping process for the Custom Views collection.

Function CreateArrayAndAddToListBox()

    Dim TheArrayCount As Integer
    Dim ListArray()

    With ActiveWorkbook

 .CustomViews.Add "Temp"

        TheArrayCount = .CustomViews.Count - 1
        ReDim Preserve ListArray(TheArrayCount)

        For n = 1 To TheArrayCount
            .CustomViews(n).Show
            ListArray(n) = .CustomViews(n).Name & _
     "  (" & ActiveSheet.Name & ")"
        Next

        For i = 1 To TheArrayCount

            For j = i + 1 To TheArrayCount

                If ListArray(i) > ListArray(j) Then
                    tVar = ListArray(i)
                    ListArray(i) = ListArray(j)
                    ListArray(j) = tVar
                End If

            Next

        Next

 .Sheets("TheChart").ListBoxes("lbShow").List = ListArray

       .CustomViews("Temp").Delete

    End With

End Function

Be aware that there are some problems in running code that shows a chart as
a
view. I experienced several system crashes, so try to avoid this scenario.
It might be preferable to use the worksheet list link, since you can include
only the custom views you want for a given chart quite easily this way.
Then,
right-click on the listbox and assign the macro shown below to it.


Sub ChangeChartView()

    Application.ScreenUpdating = False
    ThisChart = ActiveSheet.Name

    With ActiveChart.ListBoxes("lbShow")
     ActiveWorkbook.CustomViews(.List(.ListIndex)).Show
    End With

    Sheets(ThisChart).Activate
    Application.ScreenUpdating = True

End Sub

Then, by clicking on an item in the listbox, the custom view corresponding
to
the name of the item clicked will be shown. That will cause the filtering
and/or
the hiding of columns to be applied to the source data for the chart. That,
in
turn, will cause complete data points or complete data series to not appear
on
the chart. If you are using a legend on your chart, it will change to
reflect
only the data series currently appearing on the chart.