Making axis title in Excel chart with VBA

Question

I create an excel chart with VBA and then format the axis titles and fonts' size. The following code works well for the Horizontal axis

cht.SetElement msoElementPrimaryCategoryAxisTitleAdjacentToAxis
cht.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Factor of Safety"
cht.Axes(xlCategory, xlPrimary).AxisTitle.Format.TextFrame2.TextRange.Font.Size = 15

However, the similar code for the Vertical axis

cht.SetElement msoElementPrimaryValueAxisTitleAdjacentToAxis
cht.Axes(xlValue, xlPrimary).AxisTitle.Text = "Depth [mCD]"
cht.Axes(xlValue, xlPrimary).AxisTitle.Format.TextFrame2.TextRange.Font.Size = 15

leads to the error 424 "Object required". Could you please tell me what happened ?

Can you post the full code? You might have destroyed cht object by the time you get to vertical axis. Can you check if cht is still an object by the time you get to it? — Zac
– Zac, Commented Oct 4, 2018 at 11:24
@Zac: actually those two pieces of code go together and there is no code in between, that's why i can not understand and post a question here. Of course i did not make that apparent mistake. Preceding of those two part is a standard code to initiate a chart. Set cht = ActiveSheet.Shapes.AddChart2(Style:=240,XlChartType:=xlXYScatter,Left:=Cells(2, 9).Left,Top:=Cells(2, 9).Top,Width:=350,Height:=500).Chart — ctnguyen
– ctnguyen, Commented Oct 5, 2018 at 3:39
Could you try and destroy cht object and reset it before attempting to set msoElementPrimaryValueAxisTitleAdjacentToAxis? — Zac
– Zac, Commented Oct 5, 2018 at 11:41

Jon Peltier · Accepted Answer · 2018-10-10 01:10:42Z

3

In recent versions of Excel, you can use SetElement with a named constant to add features to the chart. This seems easier, but it's less intuitive in terms of what it actually does, and it can be unreliable.

So instead of this:

cht.SetElement msoElementPrimaryValueAxisTitleAdjacentToAxis
cht.Axes(xlValue, xlPrimary).AxisTitle.Text = "Depth [mCD]"
cht.Axes(xlValue, xlPrimary).AxisTitle.Format.TextFrame2.TextRange.Font.Size = 15

do this:

cht.Axes(xlValue, xlPrimary).HasTitle = True
cht.Axes(xlValue, xlPrimary).AxisTitle.Text = "Depth [mCD]"
cht.Axes(xlValue, xlPrimary).AxisTitle.Format.TextFrame2.TextRange.Font.Size = 15

answered Oct 10, 2018 at 1:10

Jon Peltier

6,1011 gold badge32 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rossv teamzelus · Accepted Answer · 2021-07-15 15:00:26Z

0

Another answer expanding off of Jon Peltier's answer:

Charts.Add
With ActiveChart        
    .Axes(xlValue, xlPrimary).HasTitle = True ' must set outside with       
    With .Axes(xlValue, xlPrimary)      
        .AxisTitle.Text = "Depth [mCD]"
        .AxisTitle.Format.TextFrame2.TextRange.Font.Size = 15       
    End With        
End With

answered Jul 15, 2021 at 15:00

rossv teamzelus

1

1 Comment

Jon Peltier Over a year ago

.HasTitle can be set within the same With block as the other statements.

haplo76 · Accepted Answer · 2022-04-12 12:39:25Z

0

I am using Office 16 and noticed that the code would appear to work if ".text" is changed to ".caption".

Sub AddAxisTitles()

'apply caption to abscissa
ActiveChart.Axes(xlValue).HasTitle = True
ActiveChart.Axes(xlValue).AxisTitle.Caption = "Connector Pitch (in.)"

'apply caption to ordinate axis
ActiveChart.Axes(xlCategory).HasTitle = True
ActiveChart.Axes(xlCategory).AxisTitle.Caption = "Station"

End Sub

edited Apr 12, 2022 at 12:39

answered Apr 12, 2022 at 11:34

haplo76

564 bronze badges

Collectives™ on Stack Overflow

Making axis title in Excel chart with VBA

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related