Thread: SetSourceData Inconsistencies

Hi all,

I have uploaded my workbook which has become a monster of a project. My issues lie (I'm guessing) in the following code

    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''Set the Chrt variable to represent the active '''
    '''chart on the worksheet "Charts"               '''
    Set Chrt = WsChrt.ChartObjects("Chart 83").Chart
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''Set the ChrtRng variable which is used to     '''
    '''set the chart source data.                    '''
    Set ChrtRng = WsChrt.Cells(30, 1).CurrentRegion
    ChrtRng.Cells.Borders.LineStyle = xlContinuous
    Chrt.SetSourceData Source:=ChrtRng.Cells

If this runs with monthly or quarterly selections, all is good but if annual selections are chosen by the user the chart does not display in the same format as monthly and quarterly. I have no idea why. To understand the differences in display, it is best seen compared to told. Here is the process:
After macros are enabled, click on the link "To activate the Industry Price Index Charting tool, please click here".
At this point, the chart should be clear but generally is not (minor issue)
Select a time range from "Monthly" or "Quarterly"
Select some indexes
Click Enter
All values display as they should
Now, click "Reset" and select a time range from "Annual"
Select some indexes
Click Enter
Notice how the chart differs in its display compared to the Monthly and Quarterly displays and I cannot figure out why since the code is the same for all 3 time range selections (see above). Lost, dazed, and confused.

IPIEsc&DeEscalator.2.zip

The years in the chart data table are being treated as a series called Industry Selection. Note it is in the legend. The axis labels are defaulting to 1,2,3,4 etc.
Quickest fix is to first change the range Annual!F6:P6 to actual dates. 1/1/2006, 1/1/2007 etc.

Then this code fix to handle dates in listbox.

Private Sub cbAnl2_Click()
    Set EndRng = WsAnl.Range("D5").CurrentRegion.Cells.Find(Year(cbAnl2.Value))
End Sub

Thanks Andy but I had them as date values before and they would display as months and years instead of just years. I had a heck of a time with displaying the proper year values.

I will give this a go now that I am at work and hopefully all turns out well.

To populate the listbox with just years use,

Private Sub cbAnl1_Click()
    Dim Ocell As Range
    Dim fndrng As Range

    Set fndrng = WsAnl.Range(WsAnl.Cells.Find(cbAnl1.Text, WsAnl.Range("D6"), xlValues, xlPart, xlByColumns, xlNext), WsAnl.Cells(7, Columns.Count).End(xlToLeft).Offset(-1, 0))
    Set StrtRng = WsAnl.Range("D5").CurrentRegion.Cells.Find(cbAnl1.Value, WsAnl.Range("D6"), xlValues, xlPart, xlByColumns, xlNext)
    For Each Ocell In fndrng
        cbAnl2.AddItem Year(Ocell.Value)
    Next Ocell
    Set Ocell = Nothing
    Set fndrng = Nothing

End Sub

and change the output number format for category labels to

                    dtRng.Cells.NumberFormat = "YYYY"

This is so close that I can almost taste it! However, the dates are now displaying as 1905 for some reason.

This is working for me. I don't think I made any other changes than those mentioned.

Okay I see why this wasn't working for me in that the worksheet "Annual" needed to have date values. I have changed them in my newest version and all seems to be well now. Once again, thanks again!

Hi Andy, did you notice that the primary x axis is displaying the dates from right to left as opposed to left to right? I tried changing that but then the primary y axis moves to the right, which I don't want. Any ideas?

You have the axis set to Display Dates in Reverse

alteration to ChartProcedure routine.

    Case 3
        With WsChrt.ChartObjects("Chart 83").Chart
            .HasTitle = True
            .ChartTitle.Text = "Annual IPI"
            .PlotBy = xlRows
            .Axes(xlCategory).ReversePlotOrder = False
        End With
    End Select

I added that line of code and it fixes the viewing order but it still flips the y axis to the right for some reason. How can I keep it on the left?

In that case also include

            .Axes(xlCategory).Crosses = xlAutomatic

Absolutely awesome, you amaze me!

And another problem I am having with these darn Y axes is: In my secondary Y axis, it may be populated with values that are the same as others, which should mean a straight line but it is instead showing each value. For instance, if there are 3 consecutive 0's to be plotted the secondary will plot a 0 higher than the other 0, which makes the series line look like it is climbing instead of being straight. Thoughts?

Can you detail how I get the chart populated to illustrate this.
I can see how the zeros for each Y axis may not be aligned but not how 3 zeros can create anything other than a horizontal line.