Chart Creation and axis labeling (code included)

[Brandon M]

So I used a macro to help me create this bit of code and I've been trying to make it work on all 36 sheets of my workbook. The major issue seems to be chart name recognition, which I thought that I'd fixed by using the ActiveChart syntax rather than the chart name excel decided to assign my chart. I can currently use the code to create my chart in each sheet, but naming the axes is giving me some problems. I'm getting a runtime error '1004' Invalid Parameter on this line:

ActiveChart.FullSeriesCollection(3).AxisGroup = 3

I'm also trying name the x axis, but haven't been able to move past the y axis stuff.

Sub Macro3()


Dim current As Worksheet
Dim chrt As ChartObject
Set WBt = ThisWorkbook
Set current = WBt.ActiveSheet


'On Error Resume Next




'
' Macro3 Macro
'


'
    current.Range("I10:O10,I12:O12,I15:O15,I17:O17").Select
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
    ActiveChart.SetSourceData Source:=current.Range( _
        "I10:O10,I12:O12,I15:O15,I17:O17" _
        )
        
    ActiveChart.FullSeriesCollection(2).AxisGroup = 2
    ActiveChart.SetElement (msoElementSecondaryValueAxisTitleAdjacentToAxis)
    ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Text = "Load Loss (lbs)"


    ActiveChart.FullSeriesCollection(3).AxisGroup = 3                                       <--error line
    ActiveChart.SetElement (msoElementSecondaryValueAxisTitleAdjacentToAxis)
    ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Text = "Temp (F)"


    
    With Selection.Format.TextFrame2.TextRange.Characters(1, 8).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
End Sub

Any help would be much appreciated.

Thanks everyone.

 

[Andrew Poulsom]

Try using SeriesCollection rather than FullSeriesCollection.

 

[Brandon M]

Hi Andrew,
Thanks for the response. I actually found a few errors in my code. I was trying to modify the secondary axis twice. After making those changes, I'm getting a new error. Runtime error '424' "Object Required"

Sub Macro3()


Dim current As Worksheet
Dim chrt As ChartObject
Set WBt = ThisWorkbook
Set current = WBt.ActiveSheet


'On Error Resume Next




'
' Macro3 Macro
'


'
    current.Range("I10:O10,I12:O12,I15:O15,I17:O17").Select
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
    ActiveChart.SetSourceData Source:=current.Range( _
        "I10:O10,I12:O12,I15:O15,I17:O17" _
        )
        
    ActiveChart.SeriesCollection(2).AxisGroup = 2
    ActiveChart.SetElement (msoElementSecondaryValueAxisTitleAdjacentToAxis)
    ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Text = "Load Loss (lbs)"


    ActiveChart.SeriesCollection(1).AxisGroup = 1
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Temp (F)"                     <-- error on this line


    
    With Selection.Format.TextFrame2.TextRange.Characters(1, 8).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
End Sub

 

[Andrew Poulsom]

You need to add an Axis Title first:

ActiveChart.Axes(xlValue).HasTitle = True