Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel Chart Formatting / Axes

Posted on 2007-11-29
10
Medium Priority
?
1,372 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

609 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