Thread: SetSourceData Filling Y-Axis Wrong

Hi all, I have a weird problem happening with a chart that I am working on in that the chart is not displaying the correct values on the y axis.

In the provided workbook, if you activate the chart tool (the last prompt on the welcome screen) a userform comes up to populate a chart. Select a date range and then 5 or so indecies from the listboxes. When you click enter, the chart area is filled but the y axis shows values different from the current region being used. The code to fill the chart is

Set ChrtRng = WsChrt.Cells(30, 3).CurrentRegion
    With WsChrt.ChartObjects("Chart 83").Chart
        .SetSourceData Source:=ChrtRng.Cells
    End With

There are arrows to click on the chart screen that cycle through chart types and when I click the right arrow, the values become proper but the first display is always wrong.
I've been trying to solve this for a few hours now without much luck. Any ideas?

Hi Mordred,
Lots of pretty code to read.

I'd be following the global variable of ChrtTogl in your globals behind your Chart Sheet.

After you press the Right Arrow you use
Chart

Objects("Chart 83").Chart.ChartType

It seems the first time into the graph it doesn't know the above.

Perhaps the Private Code, behind the Charts sheet, can't be set from the Userform code?

Hi MarvinP, thanks for the feedback. LoL, I don't know if I would call the code pretty but there it is! The original load of the chart is wrong, which is independent from the ChrtTogl variable (At least it should be). I'm stumped! Also, what is with the naming conventions and excel? I don't understand why my chart name is "Chart 83" as I have not used nor created 83 charts. Furthermore, I had originally inserted two charts, one was "Chart 14" and the other was/is "Chart 83". I inserted them one after the other and those were the names assigned by excel.

Back to the problem at hand, when the code executes via the Enter button on the Userform1, I have the code that I posted in post 1 which should default the first appearance of the chart, for which the y-axis is always wrong.

OK -
How about another guess.
At the bottom of your btnEnter_Click code you have a

Set ChrtRng = WsChrt.Cells(30, 3).CurrentRegion

I don't trust CurrentRegion to be what you just put in those cells.

Can you write a little more code to better define ChrtRng without using CurrentRegion?

I sure can, I'll do that and get back to you. Thanks MarvinP!

I found the range for the chart with a lot of elbow grease and determination but the results are the same for the first display. So I thought to myself, "why not set the Legend, XValues and values with code?" Here is what I have done up until this post (time for sleep I think)

Dim ChrtRngCnt As Long
    ChrtRngCnt = WsChrt.Range(WsChrt.Cells(31, 2), WsChrt.Cells(Rows.Count, 2).End(xlUp)).Cells.Count
    Dim SrsCnt As Long
    For SrsCnt = 0 To ChrtRngCnt - 1
        With WsChrt.ChartObjects("Chart 83").Chart
            .SeriesCollection.NewSeries
            With WsChrt.ChartObjects("Chart 83").Chart.SeriesCollection(SrsCnt)
                .Values = WsChrt.Range(WsChrt.Cells(31, 3), WsChrt.Cells(rRng, cRng))
                .Legend = WsChrt.Range(WsChrt.Cells(30 + SrsCnt, 2))
                .XValues = WsChrt.Range(WsChrt.Cells(30, 3), WsChrt.Cells(30, Columns.Count).End(xlToLeft))
                .ChartTitle.Characters.Text = "Industry Price Index Monthly"
            End With
        End With
    Next SrsCnt

This code does nothing though except set the legend to Series1 to Series(n). In other words, the previous code steps through without error but does not affect the chart.

OK - I got it!!!!

Your code is great. It does exactly what you want it to do. Excel is working just like it should but you think it should be doing something different.

And the answer is (drum roll please)....
Change your default chart type to xlLine instead of xlLineStacked. (no code needed)
The reason the y-axis is doubled (or not correct) is that Excel thinks it is supposed to show a stacked graph. by default. That means it needs to add all the values and use that as the maximum, this changes the y scale.

I ran throught the welcome screen userform and picked some simple stuff, clicked on the graph and changed it to line. The secret is to display the "Change Chart Type" Dialog Box and at the bottom of this is a button "Set as Default Chart". Click this sucker and then ok. Try again. Y-Axis is now good.
Easy Peasy Not!

Thanks a million MarvinP!!! You have spared me many hours of staring at my screen, with blood shot eyes and my chin on my hand, while drooling . I have set the default to xlLine and changed my code as per your solution and am happier than a pig in mud .

Hi Mordred,

Even though it is fun to read your pretty code, there may be better ways to do this problem.
IMO - you are stuck with a Cross Tab format of your data. Excel works a lot better with Tables that have columns heads that are independent. Because you have many columns where the heading is Dates I call this a Cross Tab Table. I've run into this so many times I've written some semi-generic code to turn Crostabs back into tables. I've done that with your data. Then (once in an Excel type table) Pivot Tables and Pivot Charts are easy.

Find the attached, where I copied you monthly sheet to a blank workbook in sheet1, I then copied sheet1 to sheet2 and run my code on it to convert it to a real Excel Table. Then did a Pivot Chart, using Excel 2010 (hope is works in 2007 also) that allows most (all) the things you are doing with your code. No Code Needed.

Using the Filters ON THE CHART ONLY you can filter dates and products. If you right click on the chart you can change Chart Type. You have a LOT of code to do things that Excel UI already does.

I hope this gives you some ideas.

HI MarvinP,

I see your point about using tables and filters. However I personally don't like the look of them and I don't know that they fully fit the requirements given to me by my department. I really liked how your chart reacted to the filtering. I plan on further checking the properties of your chart and apply them to this little monster that I am creating. All in all what I am trying to accomplish is an implementation of an economic forecasting tool that is nice on the eyes yet powerful (to those that need it) to use. Plus, and I don't know why, I would rather write procedures and try to improve my code writing because I really like doing it. What I have done so far doesn't seem to be too taxing on the system, at least not as of yet.

Thanks again for the ideas.

Michael