?
Solved

Excel Chart Formatting / Axes

Posted on 2007-11-29
10
Medium Priority
?
1,367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 375 total points
ID: 20377014
U Meant this...


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

0
 

Author Comment

by:lanexllc
ID: 20377533
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
ID: 20377547
Well try this...

xlaxisvalue.select

this will do the trick...
0
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 

Author Comment

by:lanexllc
ID: 20377604
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
ID: 20377624
Hmmm well after again i went through ur code..this one should do this...

Chart.Axes(xlAxisValue).select
0
 

Author Comment

by:lanexllc
ID: 20377947
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 375 total points
ID: 20377963
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
ID: 20382557
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
ID: 20383256
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
ID: 20383513
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

777 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