Trying to Change Y-Axis Title of Sccatter Plot to Desired Text, but nothing works. Run-time Error '424'

Question

I am trying to make a program which creates a scatter chart with specified axis titles. I initially tried to repurpose a recorded macro but I have tried many things but none of them seem to work. This is what I'm looking at right now. The first part is mostly unrelated, but I am not sure if it may be part of the issue, so it is included. I have also included stars next to the beginning of the area of interest. I am trying to get "time, s" into the x-axis, and "Theta, deg" into the y-axis. I am not a computer science degree, so please put the explaination in relatively simple terms if possible.

    Dim i As Double
    Dim CellCheck As Boolean
    Dim CountPerMISSNUM(1048574) As Double
    Dim A, B As Double
    Dim C As String
Worksheets("taboutput_withplots").Select
    Index = 1
    CurrentMISSNUM = 1
    CountPerMISSNUM(CurrentMISSNUM) = 0
    Do While Index < 1048574
    If IsEmpty(Cells(Index, 1).Value) = True Then
    Exit Do
    End If
    If Index = 1 Then CountPerMISSNUM(CurrentMISSNUM) = CountPerMISSNUM(CurrentMISSNUM) + 1
    If Index <> 1 And Cells(Index + 1, 1).Value = Cells(Index, 1).Value Then
    CountPerMISSNUM(CurrentMISSNUM) = CountPerMISSNUM(CurrentMISSNUM) + 1
    ElseIf Index <> 1 And Cells(Index + 1, 1).Value <> Cells(Index, 1).Value Then
    If IsEmpty(Cells(Index + 1, 1).Value) = False Then
    CurrentMISSNUM = CurrentMISSNUM + 1
    Else
    End If
    End If
    Index = Index + 1
    Loop
ScatterIndex = 1
    RowIndex = 0
    InitialStartingIndex = 1
    Do While ScatterIndex - 1 < CurrentMISSNUM
    If ScatterIndex = 1 Then
    RowIndex = RowIndex + CountPerMISSNUM(ScatterIndex)
    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select
    i = 1
    Do While i <= ActiveChart.SeriesCollection.Count
    ActiveChart.FullSeriesCollection(i).Delete
    Loop
'    ActiveChart.SetSourceData Source:=Range("taboutput_withplots!$A:$A")
    ActiveChart.SeriesCollection.NewSeries
    Application.Worksheets("taboutput_withplots").Select
    ActiveChart.FullSeriesCollection(ScatterIndex).Name = "=taboutput_withplots!" _
    + Range(Cells(2 + RowIndex * (ScatterIndex - 1), 1), Cells(2 + RowIndex * (InitialStartingIndex - 1), 1)).Address
    ActiveChart.FullSeriesCollection(ScatterIndex).XValues = _
    "=taboutput_withplots!" + Range(Cells(2 + RowIndex * (InitialStartingIndex - 1), 2), Cells(1 + RowIndex, 2)).Address
    ActiveChart.FullSeriesCollection(ScatterIndex).Values = "=taboutput_withplots!" _
    + Range(Cells(2 + RowIndex * (ScatterIndex - 1), 13), Cells(1 + RowIndex, 13)).Address
'***************************************************************************************************'***************************************** Area of Interest **************************************** '***************************************************************************************************
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.ChartTitle.Text = "Time vs Theta"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Time vs Theta"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 13).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 4).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
    With Selection.Format.TextFrame2.TextRange.Characters(5, 9).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
    ActiveChart.Axes(xlCategory).AxisTitle.Select
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Time, s"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Time, s"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 7).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 7).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(xlValue).AxisTitle.Select
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Theta, deg"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Theta, deg"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 10).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
    With Selection.Format.TextFrame2.TextRange.Characters(7, 4).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

Instead of getting the expected result, I get the Error '424' Object Required in return atActiveChart.Axes(xlValue).AxisTitle.Select, and I found out it works when I use xlCategoryinstead ofxlvalue, but it doesn't work for the y-axis. I tried changing xlprimarytoxlsecondaryinActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text` for the y-axis, but it spits out run-time error '-214767258(800004005)': Method 'Axes' of object'_Chart' failed.

Probably worth reviewing https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?r=Saves_AllUserSaves Avoiding use of `Select`/`Selection`/`Activate` will make your code more robust and easier to debug. — Tim Williams, Jul 18 '23 at 23:49

score 1 · Answer 1 · answered Jul 19 '23 at 01:49

Cleaner without all the Select. For example:

Sub ChartTest()
    
    Dim co As Object, cht As Chart, ax As Axis, ttl As Object
    
    Set co = ActiveSheet.Shapes.AddChart2(240, xlXYScatter) 'ChartObject
    Set cht = co.Chart   'get a reference to the contained chart
    
    'remove any "auto-plotted" series
    Do While cht.SeriesCollection.Count > 0
        cht.SeriesCollection(1).Delete
    Loop
    
    'add a series
    With cht.SeriesCollection.NewSeries
        .Name = "Testing"
        .XValues = "=Serials!A1:A12"
        .Values = "=Serials!B1:B12"
    End With
    
    'Chart title
    cht.HasTitle = True
    Set ttl = cht.ChartTitle
    ttl.Text = "Time vs Theta"
    With ttl.Format.TextFrame2.TextRange.Characters(1, 13)
        .ParagraphFormat.Alignment = msoAlignCenter
        .Font.Size = 14
    End With
    
    'X-axis title
    With GetAxisTitle(cht.Axes(xlCategory), "Time, s")
        With .Format.TextFrame2.TextRange.Font
            .Size = 10
            .Fill.Visible = msoTrue
            .Fill.ForeColor.RGB = RGB(89, 89, 89)
        End With
    End With
    
    'Y-axis title
    With GetAxisTitle(cht.Axes(xlValue), "Theta, deg")
        With .Format.TextFrame2.TextRange.Font
            .Size = 10
            .Fill.Visible = msoTrue
            .Fill.ForeColor.RGB = RGB(89, 89, 89)
        End With
    End With
    
End Sub

'Ensure an axis title exists and has the specified text, and return it
Function GetAxisTitle(obj As Axis, txt As String) As AxisTitle
    obj.HasTitle = True
    Set GetAxisTitle = obj.AxisTitle
    GetAxisTitle.Text = txt
End Function

Trying to Change Y-Axis Title of Sccatter Plot to Desired Text, but nothing works. Run-time Error '424'

1 Answers1