CODEWITHSUNDEEP
vbaexcelcharts
BruceWayne
BruceWayne

Reputation: 23285

Can't set chart Axis title (.caption)

I have a script that sets some ranges, then creates a chart. All goes well until .Axes(xlCategory, xlPrimary).Caption = "Time from Sent to Rec'd" where I get the error "Run-time error '438': Object doesn't support this property or method".

Here's my code:

Sub CreateChart()
dim avgWS as worksheet: set avgWS = activesheet
...[code here, setting the ranges and such].... 

        ''' TIME TO CREATE THE CHART!!
        with avgws
           Dim newChart As Chart
      '   Set newChart = Charts.Add
        Set newChart = Charts.Add.Location(xlLocationAsObject, avgWS.name)
        With newChart
            .ChartType = xlLineMarkers
            .SeriesCollection.NewSeries
            With .SeriesCollection(1)
                .name = chartName
                .Values = thePeopleChartValues
            End With
            .SeriesCollection.NewSeries
            With .SeriesCollection(2)
                .name = avgWS.Cells(1, dayLimitCol).Value
                .Values = dayLimitValues
            End With
            .SeriesCollection.NewSeries
            With .SeriesCollection(3)
                .name = avgWS.Cells(1, overalltheAvgCol).Value
                .Values = theAverages
            End With
            .HasTitle = True

            .Axes(xlCategory).CategoryType = xlCategoryScale
            .SetElement (msoElementPrimaryValueAxisTitleRotated)
            .Axes(xlCategory, xlPrimary).Caption = "Time from the Sent to Shares Rec'd"  '''' ERROR HERE!!
            .SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
            .Axes(xlCategory).Caption = "the Date"
            .SetElement (msoElementChartTitleCenteredOverlay)
            .Axes(xlCategory).Caption = "='CY 2014-15B Averages'!R1C2"
            .SetElement (msoElementLegendRightOverlay)
            .SetElement (msoElementLegendBottom)
        End With

I've only included the Chart part, but if you would like/need more of the code just let me know. I have ranges and such set up, and get no errors there. It's only when I try to create the .Captions. If I walk through the code, via F8 and skip the three .caption lines, the chart gets created like I want....just how do I set those Axis captions? The macro recorder isn't much help (that's how I got to where I am now).

edit: Hm, if I use

.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "whatever whatever"

it sets the horizontal axis caption to "Whatever whatever". I guess I'm making progress, but I want that to be the vertical title, not horizontal and can't figure out how to do so.

Upvotes: 1

Views: 2096

Answers (1)

xidgel
xidgel

Reputation: 3145

Before you start formatting the axis title, create it:

.Axes(xlCategory, xlPrimary).HasTitle = True

Then to access the caption make sure you go through the AxisTitle object:

.Axes(xlCategory, xlPrimary).AxisTitle.Caption = ""

Then use

.Axes(xlCategory, xlPrimary).AxisTitle.Orientation = xlVertical

Hope that helps.

Upvotes: 3

Related Questions