CODEWITHSUNDEEP
excelvbacharts
Dan
Dan

Reputation: 185

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

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

enter image description here

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:

enter image description here

enter image description here

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

Upvotes: 1

Views: 1038

Answers (2)

user11982798
user11982798

Reputation: 1908

 '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

enter image description here

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

Upvotes: 1

Dan
Dan

Reputation: 185

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

Upvotes: 0

Related Questions