EXCEL VBA: How to Programmatically Change .Placement Property of a Chart by Name

Question

I have the following code where I am attempting to change the .Placement property of a chart entitled "MyChart".

The highlighted line results in an error stating "The item with the specified name was not found."

As you can see from the picture, I also tried:

MyChart.Placement = xlFreeFloating

However, it is my understanding that .Placement is a property of a ChartObject and not a Chart itself.

Here is the code from the pic above:

 'Format Main Title
  MyChart.ChartTitle.Font.Size = 14
  MyChart.ChartTitle.Font.Bold = True
  MyChart.ChartTitle.Font.Name = "TimesNewRoman"

  'Set FreeFloating Property
  ActiveSheet.ChartObjects("MyChart").Placement = xlFreeFloating

  'MyChart.Placement = xlFreeFloating

How can I change the .Placement property for MyChart (that is, How can I change the .Placement property for a particular chart by name)? Any assistance is appreciated.

===========================================================================

In response to the solution offered: When I try this code, I get this error message:

I don't understand how the name of the graph could be anything but "MyChart". Here is the code that creates this graph:

Set MyChart = ActiveSheet.ChartObjects.Add(Left:=Cells(Row0 + (j3 - 1) * 22, Col0 + ChartStart + 1 + (k3 - 1) * 7).Left, _
                        Top:=Cells(Row0 + (j3 - 1) * 22, Col0 + ChartStart + 1 + (k3 - 1) * 7).Top, _
                        Width:=310, _
                        Height:=300).Chart

                    MyChart.ChartType = xlXYScatter
                    MyChart.HasLegend = False

                    With MyChart
                        'XAxis Label
                        .Axes(xlCategory, xlPrimary).HasTitle = True
                        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = XName(i3, 1)
                        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Font.Bold = True
                        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Font.Name = "TimesNewRoman"
                        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Font.Size = 10
                        'YAxis Label
                        .Axes(xlValue, xlPrimary).HasTitle = True
                        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = YName
                        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Font.Bold = True
                        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Font.Name = "TimesNewRoman"
                        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Font.Size = 10
                    End With

                    'Remove Gridlines
                    For Each axs In MyChart.Axes
                        axs.HasMajorGridlines = False
                        axs.HasMinorGridlines = False
                    Next

                    With MyChart.SeriesCollection.NewSeries
                        .Name = "Scatterplot of " & YName & " by " & XName(i3, 1)
                        .Values = y5
                        .XValues = WorksheetFunction.Index(X4, 0, i3 + 1)
                        'Format Markers
                        .MarkerBackgroundColorIndex = 2
                        .MarkerForegroundColorIndex = 5
                        .MarkerStyle = 8
                    End With

                    '5% around the edges
                    'XXX 999

                    'Vertical Axes NOT at zeros

                    If WorksheetFunction.Min(WorksheetFunction.Index(X4, 0, i3 + 1)) < 0 Then
                        MyChart.Axes(xlCategory).CrossesAt = MyChart.Axes(xlCategory, xlPrimary).MinimumScale
                    End If

                    'Format Main Title
                    MyChart.ChartTitle.Font.Size = 14
                    MyChart.ChartTitle.Font.Bold = True
                    MyChart.ChartTitle.Font.Name = "TimesNewRoman"

                    'Set FreeFloating Property
                    ActiveSheet.ChartObjects("MyChart").Placement = Excel.XlPlacement.xlFreeFloating

                    'MyChart.Placement = xlFreeFloating


                    'Set FreeFloating Property
'                    ActiveChart.Select
'                    Selection.Placement = xlFreeFloating

MyChart is a chart variable. It is not the name of the chart or of the chart object. Just use MyChart.Parent.Placement = xlFreeFloating. — Jon Peltier
– Jon Peltier, Commented Jan 12, 2020 at 19:22

user11982798 · Accepted Answer · 2020-01-09 00:31:14Z

1

 'Format Main Title
  MyChart.ChartTitle.Font.Size = 14
  MyChart.ChartTitle.Font.Bold = True
  MyChart.ChartTitle.Font.Name = "TimesNewRoman"

  'Set FreeFloating Property
  ActiveSheet.ChartObjects(MyChart.name).Placement = Excel.XlPlacement.xlFreeFloating

  'In mine this is running too
  'ActiveSheet.ChartObjects(MyChart.name).Placement = xlFreeFloating

but you must ensure the chart name is same as displayed in the left of formula bar

First method, compare mychart.name with ActiveSheet.ChartObjects.chart.Name and use ActiveSheet.ChartObjects

For Each thechart In ActiveSheet.ChartObjects
    If thechart.Chart.Name = myChart.Name Then
       myChartName = thechart.Name

       thechart.Placement = Excel.XlPlacement.xlFreeFloating

       Exit For
    End If
Next

Second method, use myChart.parent

  myChart.Parent.Placement = Excel.XlPlacement.xlFreeFloating

edited Jan 9, 2020 at 0:31

answered Jan 8, 2020 at 4:26

user11982798

1,8981 gold badge8 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Dan Over a year ago

Thanks for the line of code. Unfortunately, I am still getting an error. I have revised the initial post with more info on the new line of code.

user11982798 Over a year ago

Looks like your chart name is different, usually the name chart is chart1, chart2 etc. As said on message error, indicated mychart is not found

user11982798 Over a year ago

Try to check the letter Case, compare your chart name displayed ini formula bar and in your script

user11982798 Over a year ago

Ok Info see, mychart is not your chart name, just variable

Dan Over a year ago

When I use MyChart.Name, it returns the sheet name AND the chart name. For example: Sheet 1 Chart 1. Therefore I had to split the string. My code solution is posted. Thanks for your help!!!

|

Dan · Accepted Answer · 2020-01-08 23:05:10Z

0

This is the code I developed with the help of user11982...

MyChart.Name returns the sheet name and the chart name:

MyChart.Name = Sheet 1 Chart 2

Because I just want the chart name:

            txtary = Split(MyChart.Name, " ")
            ChartName = txtary(2) & " " & txtary(3)

            'Set FreeFloating Property
            ActiveSheet.ChartObjects(ChartName).Placement = Excel.XlPlacement.xlFreeFloating

answered Jan 8, 2020 at 23:05

Dan

1851 gold badge6 silver badges19 bronze badges

Collectives™ on Stack Overflow

EXCEL VBA: How to Programmatically Change .Placement Property of a Chart by Name

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related