How to specify the chart title (caption) and y axis label or an embedded chart in VBA?

[OaklandJim]

There is a steep learning curve for me trying to figure out how to create an embedded chart.

I want to specify the chart title and y axis label for an embedded chart.

When I try to use .Caption = "Chart Title" I get an error as shown below.

Also shown are attempts at setting the y axis title with error messages.

Here is the relevant code.

    Set wsData = ThisWorkbook.Worksheets("Sheet1") '<= worksheet containing data
   
    Set rAnchorCellData = wsData.Range("A1")   '<= this is the cell containing the ECR header

    Set cResults = wsData.Shapes.AddChart.Chart

    With cResults
       
        .Parent.Name = "ECR Counts"
'
        .ChartType = xlColumnClustered
       
        .SetSourceData Source:=wsData.Range("B2:F3")
       
'       Error Mthod or data member not found
        '.Caption = "ECR Counts"
       
        .Axes(xlValue).MajorUnit = 1
       
'       Error 424: Object required
'        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "ECRs"
       
    End With
   
    With wsChart.ChartObjects("ECR Counts")
        .Top = rAnchorCellChartData.Offset(0, -1).Top
        .Left = rAnchorCellChartData.Offset(0, -1).Left
       
'       Error 438: Object does not support this property or method
'        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "ECRs"
     
    End With

 

[OaklandJim]

Dominic already showed me how to set the chart title. Just need help with axis labeling.

 

[OaklandJim]

I found an answer that is not satisfying but it works. It involves activating the chart to add x axis and y axis labels. I'd like to know why I cannot set those values using the chart object created. Instead I have to specify a Worksheet.ChartObjects(sChartName) then activate it.

'   Create the new chart.
    Set cResults = wsChart.Shapes.AddChart.Chart
    
'   Set some characteristics of the chart.
    With cResults
        
'       Give the chart a name.
        .Parent.Name = sChartName
        
'       Specify the chart type.
        .ChartType = xlColumnClustered
        
'       Specify the location of the data.
        .SetSourceData Source:=rChartDataRange
        
'       Set the chart title
        .HasTitle = True
        .ChartTitle.Caption = sChartTitle
        
'       Set the major units to 1 for "integers"
        .Axes(xlValue).MajorUnit = 1
        
'       Delete the legend for the chart because there is only one data series.
        .HasLegend = False
        
    End With
    
'   More chart formatting.
    With wsChart.ChartObjects(sChartName)
    
'       Put the chart just below the data.
        .Top = rAnchorCellChartData.Offset(2, -1).Top
        .Left = rAnchorCellChartData.Offset(2, -1).Left
        
        Application.ScreenUpdating = True
        
'       Activate the chart to format axes.
        .Activate
        
'       Put the series label into the charts Y axis.
        With ActiveChart.Axes(xlCategory)
            .HasTitle = True
            With .AxisTitle
            .Caption = "ECRs"
            End With
        End With

'       Put the label into the chart's X axis.
        With ActiveChart.Axes(xlValue)
            .HasTitle = True
            With .AxisTitle
            .Caption = "Counts"
            End With
        End With
    
    End With

 

[Domenic]

Try it like this...

'   Create the new chart.
    Set cResults = wsChart.Shapes.AddChart.Chart
    
'   Set some characteristics of the chart.
    With cResults
        
'       Give the chart a name.
        .Parent.Name = sChartName
        
'       Specify the chart type.
        .ChartType = xlColumnClustered
        
'       Specify the location of the data.
        .SetSourceData Source:=rChartDataRange
        
'       Set the chart title
        .HasTitle = True
        .ChartTitle.Caption = sChartTitle
    
        .HasAxis(xlCategory, xlPrimary) = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "ECRs"
        
        .HasAxis(xlValue, xlPrimary) = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Counts"

'       Set the major units to 1 for "integers"
        .Axes(xlValue).MajorUnit = 1
        
'       Delete the legend for the chart because there is only one data series.
        .HasLegend = False
        
    End With

Hope this helps!

 

[OaklandJim]

Dominic. Thank you very much for the help!

BTW, when I post it is usually when I'm trying to help another person on the list.

I still get the Run-time error 424 Object required on this code line.

.Axes(xlCategory, xlPrimary).AxisTitle.Text = "ECRs"

It's as if Excel is not recognizing the chart created as an object, which of course it is.

The workbook is HERE.

 

[Domenic]

Sorry, my mistake, it should be .HasTitle, not .HasAxis . . .

        With .Axes(xlCategory, xlPrimary)
            .HasTitle = True
            .AxisTitle.Caption = "ECRs"
        End With
        
        With .Axes(xlValue, xlPrimary)
            .HasTitle = True
            .AxisTitle.Caption = "Counts"
        End With

Hope this helps!

 

[OaklandJim]

Yes. That did it. Thanks very much.

 

[Domenic]

You're very welcome, I'm glad I could help.

By the way, somehow I missed your last reply, hence the delay in my response.

Cheers!