CODEWITHSUNDEEP
excelvba
user3470049
user3470049

Reputation: 447

Run-time Error '424': Object Required when setting AxisTitle.Text

I edited a graph and captured a macro while doing so. Then I wanted to copy and paste the vba to some other vba file. I did not change anything and kept the whole code. But at the following line always there is the Object required error (424):

ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Kraft"

The context is:

ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Messung 1"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Messung 1"
With Selection.Format.TextFrame2.TextRange.Characters(1, 9).ParagraphFormat
    .TextDirection = msoTextDirectionLeftToRight
    .Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 9).Font
    .BaselineOffset = 0
    ...
End With
ActiveChart.ChartArea.Select
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
ActiveChart.Axes(xlCategory).AxisTitle.Select
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Kraft"

I hope someone can help.

Upvotes: 3

Views: 6828

Answers (3)

Xin Meng
Xin Meng

Reputation: 2110

Try the code below, and I have tested the method and it works on Office 365 Excel(Latest version).

ActiveSheet.ChartObjects("Chart Name").Activate
With ActiveChart
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X Axis Title"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y Axis Title"
End With

By the way, I also found that the Macro Recorded by Excel for ActiveChart will return this error. I found this link and some explanation of ActiveChart of VBA, maybe it will help.

There are two distinct types of charts in Excel: 1. One is a chart consisting of an entire sheet (a chart sheet). A chart sheet is not a worksheet. Charts(1) is the first chart sheet in the workbook. 2. The other is a chart placed on top of a worksheet - "embedded" on the worksheet. The worksheet chart has a container around it called a "Chart Object". Therefore, you have to identify the chart object before you can specify the particular chart you want to refer to...ActiveSheet.ChartObjects(1).Chart.

If you want to create a separate chart sheet then your recorded code could be modified to retain the original sheet as the source for the chart data. When you add a chart sheet it becomes the active sheet.

By the way, you can check the Chart name from the Selection Pane

enter image description here enter image description here

Upvotes: 1

emmistar
emmistar

Reputation: 101

Although you have recorded using a macro and it should automatically play back, it doesn't seem to in the case of label text (I had the same problem).

The solution is to use this code instead, gathered from the microsoft website

With Charts("Chart1").Axes(xlCategory) 
    .HasTitle = True 
    .AxisTitle.Text = "July Sales" 
End With

https://msdn.microsoft.com/en-us/library/office/ff839276.aspx

I also found the solution in this stack overflow post Excel 2007 VBA Problem setting Axis Title

Upvotes: 3

Tim Williams
Tim Williams

Reputation: 166790

ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Kraft"
                                               ^^^^^^^^^^

Upvotes: -1

Related Questions