CODEWITHSUNDEEP
vbaexcel
Anthrochange
Anthrochange

Reputation: 45

Run-Time error 424 - Object required error

I used a record macro on excel 2013 to select data and make a bar graph. My exact actions were I selected A1, ctrl+shift+arrow down, ctrl+shift+arrow right, insert 2d bar graph, pressed the green + sign that says chart elements, selected Axis Titles, named the axis titles and the chart title, and stopped recording the macro. I get a 424 error Object required when the code gets to the point where I label the y(value) axis. I've tried renaming this 1st and 2nd and get the same error regardless. Here is the actual code:

Sub BarGraph()
'
' BarGraph Macro
'

'
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$D$5")
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Sales"        'This is the line that the failure starts at
Selection.Format.TextFrame2.TextRange.Characters.Text = "Total Sales"
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 = msoFalse
    .NameComplexScript = "+mn-cs"
    .NameFarEast = "+mn-ea"
    .Fill.Visible = msoTrue
    .Fill.ForeColor.RGB = RGB(89, 89, 89)
    .Fill.Transparency = 0
    .Fill.Solid
    .Size = 10
    .Italic = msoFalse
    .Kerning = 12
    .Name = "+mn-lt"
    .UnderlineStyle = msoNoUnderline
    .Strike = msoNoStrike
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Region"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Region"
With Selection.Format.TextFrame2.TextRange.Characters(1, 6).ParagraphFormat
    .TextDirection = msoTextDirectionLeftToRight
    .Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 6).Font
    .BaselineOffset = 0
    .Bold = msoFalse
    .NameComplexScript = "+mn-cs"
    .NameFarEast = "+mn-ea"
    .Fill.Visible = msoTrue
    .Fill.ForeColor.RGB = RGB(89, 89, 89)
    .Fill.Transparency = 0
    .Fill.Solid
    .Size = 10
    .Italic = msoFalse
    .Kerning = 12
    .Name = "+mn-lt"
    .UnderlineStyle = msoNoUnderline
    .Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Total Sales"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Total Sales"
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 = msoFalse
    .NameComplexScript = "+mn-cs"
    .NameFarEast = "+mn-ea"
    .Fill.Visible = msoTrue
    .Fill.ForeColor.RGB = RGB(89, 89, 89)
    .Fill.Transparency = 0
    .Fill.Solid
    .Size = 14
    .Italic = msoFalse
    .Kerning = 12
    .Name = "+mn-lt"
    .UnderlineStyle = msoNoUnderline
    .Spacing = 0
    .Strike = msoNoStrike
End With
Range("K7").Select
End Sub

I also tried adding the title individually from the design tab by selecting add chart element, axis title, primary vertical and got the same problem. I'm really not sure what the problem could be so some help would be much appreciated, thank you.

Upvotes: 1

Views: 1302

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149295

You are getting that error because you are trying to write to the Title and, since it is not able to find it, it is giving you an Object required error (424). Try this

ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Total Sales"

Also please avoid the use of ActiveChart/Select etc. Work with Objects.

Interesting Read on How to avoid using Select in Excel VBA macros

Upvotes: 3

Liss
Liss

Reputation: 441

According to MSDN, you have to make sure the .HasTitle is set to true first. Add this line to the one before it fails.

ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True

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

Upvotes: 4

Related Questions