Solved

Excel Chart Formatting / Axes

Posted on 2007-11-29
10
1,322 Views
Last Modified: 2008-02-01
I'm running into a problem with Excel 2003 and VB.NET 2003.   My code is working mostly, but I'm having some trouble formating certain elements of the chart object.  The main problem is that I need the lables on the axis rotated using the orientation value.    I've included the code used to build the chart below, what I can't figure out is how to make the X-Axes values rotate 90 degrees.

Any help is greatly appreciated.
       Dim RangePeriod As Object

        Dim RangePercent As Object

        Dim finalCell As String = "C" & ds.Tables(0).Rows.Count + 1

        Dim FinalPctCell As String = "H" & ds.Tables(0).Rows.Count + 1

 

        '' set the range value

        RangePeriod = worksheet.Range("C2", finalCell)

        RangePercent = worksheet.Range("H2", FinalPctCell)

 

        ' The following code draws the chart

        Dim chartobjects As Object

        chartobjects = worksheet.ChartObjects(Missing.Value)

 

        Dim chartobject As Object

         ''chartObjects.Add(Left,Top,Width,Height)

        chartobject = chartobjects.Add(0, 0, 750, 650)

 

        Dim chart As Object

        chart = chartobject.Chart

 

        Dim source As Object = RangePercent

        Dim Gallery As Object = Excel.XlChartType.xl3DColumn

        Dim Format As Object = Missing.Value

        Dim PlotBy As Object = Excel.XlRowCol.xlColumns

        Dim CategoryLabels As Object = 0

        Dim SeriesLables As Object = 0

        Dim HasLegend As Object = False

        Dim Title As Object = "Chart"

        Dim ValueTitle As Object = "Dates"

        Dim ExtraTitle As Object = "Percent"

 

        Try

            chart.ChartWizard(source, Gallery, Format, PlotBy, CategoryLabels, SeriesLables, HasLegend, Title, _

                ValueTitle, ExtraTitle)

 

        Catch ex As Exception

            Throw ex

        End Try

 

 

        Dim oSeries As Excel.Series

        oSeries = chart.SeriesCollection(1)

        oSeries.XValues = RangePeriod

 

        Dim xlAxisCategory, xlAxisValue

 

        xlAxisCategory = CType(chart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)

        xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True

 

        xlAxisValue = CType(chart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)

        xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True

Open in new window

0
Comment
Question by:lanexllc
  • 5
  • 5
10 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 125 total points
Comment Utility
U Meant this...


    ActiveChart.Axes(xlValue).Select
    With Selection.TickLabels
        .ReadingOrder = xlContext
        .Orientation = xlUpward

0
 

Author Comment

by:lanexllc
Comment Utility
Perhaps I'm missing something obvious but when I add:
ActiveChart.Axes(xlAxisValue).select

I get: 'ActiveChart.Axes' is not declared or the module containing it is not loaded in the debugging session.
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Well try this...

xlaxisvalue.select

this will do the trick...
0
 

Author Comment

by:lanexllc
Comment Utility
hmm, tried that earlier and got

xlaxisvalue.select

Run-time exception thrown : System.MissingMemberException - Public member 'select' on type 'Axes' not found.

I must have it defined it incorrectly.  Is there no simple method to SELECT the values of one the Axes?
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Hmmm well after again i went through ur code..this one should do this...

Chart.Axes(xlAxisValue).select
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:lanexllc
Comment Utility
again, I know i'm missing the obvious, but that causes the following exception:

System.Runtime.InteropServices.COMException - Unable to get the Axes property of the Chart class

Not sure if it helps but here are the last few lines where it's breaking.


        Dim xlAxisCategory, xlAxisValue
 

        xlAxisCategory = CType(chart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)

        xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
 

        xlAxisValue = CType(chart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)

        xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
 

        chart.ChartArea.Border.Weight = Excel.XlBorderWeight.xlThick
 

        '' THE FOLLOWING LINE CAUSES THE EXCEPTION

        chart.Axes(xlAxisValue).select()

        '' end

Open in new window

0
 
LVL 59

Assisted Solution

by:Saurabh Singh Teotia
Saurabh Singh Teotia earned 125 total points
Comment Utility
Well if i go through the code may be coz u have defined chart as a object and one of the possible reasons coz of which its giving u an error...
0
 

Author Comment

by:lanexllc
Comment Utility
That makes sense, but I've yet to see a better way of defining the objects.  Any advise on where to check?

Thanks!
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Well lets go back to basic...try making a chart by recording macro...by making a chart...and wherever the data points are variable..lets define it to what we want to do...and by this way this thing will be resolved...
0
 

Author Comment

by:lanexllc
Comment Utility
We'll after messing around with it some more, the issue was indeed the method in which I was creating the objects.  By declaring them as the proper types, I was finally able to select the axis, and make the changes required.

See Code:
        Dim xlAxisCategory As Excel.Axes

        xlAxisCategory = chart.Axes(, Excel.XlAxisGroup.xlPrimary)

        xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
 

        xlAxisCategory.Item(Excel.XlAxisType.xlValue).Select()
 

        With app.Selection.TickLabels

            .Orientation = Excel.XlOrientation.xlDownward

        End With

Open in new window

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now