CODEWITHSUNDEEP
excelchartsvba
hakandeep
hakandeep

Reputation: 51

Macro gives error while adding the second axis title to the chart?

ActiveSheet.Shapes.AddChart2(227, xlLine).Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(1).Name = "=Sheet1!$C$4"
    ActiveChart.FullSeriesCollection(1).Values = "=Sheet1!$D$4:$AC$4"
    ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$D$3:$AC$3"    

ActiveSheet.ChartObjects("Chart 2").Activate
        ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
        ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
        ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Consumption"
        Selection.Format.TextFrame2.TextRange.Characters.Text = "Consumption"
        With Selection.Format.TextFrame2.TextRange.Characters(1, 11).ParagraphFormat
            .TextDirection = msoTextDirectionLeftToRight
            .Alignment = msoAlignCenter
        End With
        With Selection.Format.TextFrame2.TextRange.Characters(1, 11).Font
            .BaselineOffset = 0
            .Bold = msoTrue
            .NameComplexScript = "+mn-cs"
            .NameFarEast = "+mn-ea"
            .Fill.Visible = msoTrue
            .Fill.ForeColor.RGB = RGB(89, 89, 89)
            .Fill.Transparency = 0
            .Fill.Solid
            .Size = 12
            .Italic = msoFalse
            .Kerning = 12
            .Name = "+mn-lt"
            .UnderlineStyle = msoNoUnderline
            .Strike = msoNoStrike
        End With
        ActiveChart.Axes(xlCategory).AxisTitle.Select
        ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Weeks"
        Selection.Format.TextFrame2.TextRange.Characters.Text = "Weeks"
        With Selection.Format.TextFrame2.TextRange.Characters(1, 5).ParagraphFormat
            .TextDirection = msoTextDirectionLeftToRight
            .Alignment = msoAlignCenter
        End With
        With Selection.Format.TextFrame2.TextRange.Characters(1, 5).Font
            .BaselineOffset = 0
            .Bold = msoTrue
            .NameComplexScript = "+mn-cs"
            .NameFarEast = "+mn-ea"
            .Fill.Visible = msoTrue
            .Fill.ForeColor.RGB = RGB(89, 89, 89)
            .Fill.Transparency = 0
            .Fill.Solid
            .Size = 12
            .Italic = msoFalse
            .Kerning = 12
            .Name = "+mn-lt"
            .UnderlineStyle = msoNoUnderline
            .Strike = msoNoStrike
        End With
        Range("H7").Select
        ActiveSheet.ChartObjects("Chart 2").Activate
        ActiveSheet.Shapes("Chart 2").IncrementLeft -206.25
        ActiveSheet.Shapes("Chart 2").IncrementTop -33.75
        ActiveSheet.ChartObjects("Chart 2").Activate
        ActiveSheet.Shapes("Chart 2").ScaleWidth 2.3979166667, msoFalse, _
            msoScaleFromTopLeft
        ActiveChart.SetElement (msoElementDataLabelTop)

hey everyone, I constructed a chart using record macro. The problem is macro can not construct one of the axis titles therefore it gives error. Also note that when you construct a chart, it comes with no axis titles. When you press "+" you can add axis titles and you will have two of them. In code it also seems that it is trying to add but in real it doesn't work at all. Since I can't see any code errors, I can not find any solution. Maybe you guys can see something or you know better ways to add titles. Thanks

Upvotes: 1

Views: 1128

Answers (1)

Tyeler
Tyeler

Reputation: 1118

Hakandeep!

Not sure how this has gone 14 hours without any help, but here you go. This should completely fix your problem.

Sub MakinAChart()
    Dim mychart As ChartObject
    Set mychart = ThisWorkbook.Sheets(1).ChartObjects("Chart 1")

    With mychart.Chart
        .Axes(1, 1).HasTitle = True 
        .Axes(1, 1).AxisTitle.Text = "bottom text" '1 = xlCategory, 1 = xlPrimary
        .Axes(2, 1).HasTitle = True
        .Axes(2, 1).AxisTitle.Text = "left text" '2 = xlValue, 1 = xlPrimary
    End With
End Sub

Charts generally have the x axis title enabled as default. The y axis however is not, unless you were to click on it. Because of the way that Excel's chart UI behaves, it doesn't implicitly set this as true when viewed via recording a macro.

You can also insert ActiveChart.Axes(2, 1).HasTitle = True at any point before naming the axis. Good form would be to do the same for your x axis as well.

Cheers!

Upvotes: 1

Related Questions