VBA Chart wizard - Insert Footer

[PRodgers4284]

I have a macro to create a chart dynamically and i want to be able to add a footer or text to the bottom of the chart if possible, i would like to know what the property is to do this, my code is below:

Sub CreateChart()

         ' Select the cell in the upper-left corner of the chart.
   
         Range("Data").Select
                 mysheetname = ActiveSheet.Name

         ' Add a chart to the active sheet.
         ActiveSheet.ChartObjects.Add(550.122, 100, 400.5, 155.25).Select

         Application.CutCopyMode = False

         ActiveChart.ChartWizard _
            Source:=Sheets(mysheetname).Range("Data"), _
            Gallery:=xlColumn, Format:=1, PlotBy:=xlColumns, _
            CategoryLabels:=2, SeriesLabels:=1, HasLegend:=0, _
            Title:=Sheets(mysheetname).Range("ChartName").Value, CategoryTitle:="", _
            ValueTitle:="", ExtraTitle:=""
        
        'Chart Border
        With ActiveChart.ChartArea
        .Border.LineStyle = xlMedium
        End With
        
         'Chart Title
            With ActiveChart.ChartTitle
            .Font.Size = 8
            End With
         
        'Chart Width/Height
            ActiveChart.Parent.Width = 906
            ActiveChart.Parent.Height = 450
    
        'Series 1
            ActiveChart.SeriesCollection(1).Interior.ColorIndex = 50 'Series Column Green
            ActiveChart.ChartGroups(1).Overlap = 100
            With Selection
            End With
            
        'Series 2
            ActiveChart.SeriesCollection(2).Interior.ColorIndex = 3 'Series Column Red
            With Selection
              '.Border.Weight = xlMedium
              '.Border.ColorIndex = 1 'White Border
            End With
                        
        'Format Y Axis
            With ActiveChart.Axes(xlValue)
            .MinimumScale = 0
            .MinorUnit = 10
            .MajorUnit = 10
            .MaximumScale = 100
            .TickLabels.Font.Size = 8
            End With
                     
        'Format X Axis
            With ActiveChart.Axes(xlCategory)
            .TickLabels.Font.Size = 8
            End With
               
      End Sub

[Andy Pope]

You can use axis titles, if not already being used.

Or a textbox.

Macro recorder should give you the code you need.

[PRodgers4284]

You can use axis titles, if not already being used.

Or a textbox.

Macro recorder should give you the code you need.

Thanks Andy

[PRodgers4284]

I have added this line of the code, but im not sure if im on the right track, i cant say any textbox on the chart.

   
With ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 100.75, 11.25, 57.75, 21#)
End With

[Andy Pope]

Looks okay to me.

Sub ChartFooter()

    With ActiveChart
        With .Shapes.AddTextbox(msoTextOrientationHorizontal, _
                                .PlotArea.InsideLeft, _
                                .ChartArea.Top + .ChartArea.Height - 20, _
                                .PlotArea.InsideWidth, _
                                20)
            .Name = "Footer"
            .TextFrame.Characters.Text = "This is my footer"
        End With
    End With
                                                                
End Sub

[PRodgers4284]

Andy i cant see the footer on the chart, am i missing something?

[Andy Pope]

Hard to tell. Can you post example workbook where the chart is created but the footer does not appear.

[PRodgers4284]

Hard to tell. Can you post example workbook where the chart is created but the footer does not appear.

Andy i have it working , it was the size of the chart inside the userform window that i needed to adjust. Many thanks for your help Andy.

[Andy Pope]

            .TextFrame.Characters.Text = "This is my " & range("B2").value & " footer"

[PRodgers4284]

Andy im trying to adjust the footer placement at the bottom of the chart, the textbox is appearing at the very bottom along the chart border, what is the syntax to change the bottom indent?

[Andy Pope]

This part adds the textbox with the Left,Top,Width,Height values calculated from the charts properties.

So change the 3rd argument.

        With .Shapes.AddTextbox(msoTextOrientationHorizontal, _
                                .PlotArea.InsideLeft, _
                                .ChartArea.Top + .ChartArea.Height - 20, _
                                .PlotArea.InsideWidth, _
                                20)

[PRodgers4284]

Andy im getting a error with "AddTextbox" stating ("Wrong number of arguments or invalid property assignment") with the code below:

With ActiveChart
With .Shapes.AddTextbox(msoTextOrientationHorizontal, _
.PlotArea.InsideLeft + 40, _
.ChartArea.Top - 1, _
.ChartArea.Height - 5, _
.PlotArea.InsideWidth - 100, _
40)

[Andy Pope]

Please add code tags to your previous post.


Because you have 5 arguments.

With .Shapes.AddTextbox(msoTextOrientationHorizontal, _   ' Layout
.PlotArea.InsideLeft + 40, _                                            ' Left  
.ChartArea.Top - 1, _                                                    ' Top
.ChartArea.Height - 5, _                                                 ' Width
.PlotArea.InsideWidth - 100, _                                         ' Height 
40   )                                                                         ' Error!!

I would guess you don't want the second top calculation.

With .Shapes.AddTextbox(msoTextOrientationHorizontal, _
.PlotArea.InsideLeft + 40, _
.ChartArea.Top - 1, _
.PlotArea.InsideWidth - 100, _
40)

[PRodgers4284]

Got it working