CODEWITHSUNDEEP
excelvbacharts
ctnguyen
ctnguyen

Reputation: 129

Making axis title in Excel chart with VBA

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 ?

Upvotes: 3

Views: 16302

Answers (3)

haplo76
haplo76

Reputation: 56

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

Upvotes: 0

rossv teamzelus
rossv teamzelus

Reputation: 1

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

Upvotes: 0

Jon Peltier
Jon Peltier

Reputation: 6053

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

Upvotes: 3

Related Questions