CODEWITHSUNDEEP
excelvbachartsposition
Elm
Elm

Reputation: 1407

defining vba chart location

I'm new to vba and am trying to position a vba chart on an excel page. Below is the code. If I replace:

Set c = c.Location(Where:=xlLocationAsObject, Name:=chLoc.Parent.Name)

with 

Set c = c.Location(Where:=xlLocationAsObject, Name:="Sheet1")

then the code works. Otherwise I get 'error 1004 Method 'Range' of Object' _Worksheet failed' and the error occurs on line:

   Set chLoc = sh2.Range(sh2.[a1:g10])

Here is the code - thanks and if you have good resource for vba charts I would appreciate any direction:

Sub Chart()

    Dim sh1 As Worksheet
    Dim sh2 As Worksheet

    Dim xaxis As Range
    Dim yaxis As Range

    Set sh1 = ActiveWorkbook.Sheets("Spon Email Performance Graph")
    Set sh2 = ActiveWorkbook.Sheets("Graphs")

    Set xaxis = sh1.Range(sh1.[b15], sh1.[b15].End(xlDown))
    Set yaxis = sh1.Range(sh1.[g15], sh1.[g15].End(xlDown))

    Dim chLoc As Range
    Set chLoc = sh2.Range(sh2.[a1:g10])

    ' Worksheets("Graphs").ChartObjects.Delete

    Dim c As Chart
    Set c = Charts.Add
    Set c = c.Location(Where:=xlLocationAsObject, Name:=chLoc.Parent.Name)
    With c
        .ChartType = xlColumnClustered
        ' set other chart properties

    End With

    Dim s As Series
    Set s = c.SeriesCollection.NewSeries
    With s
        .Values = yaxis
        .XValues = xaxis
        ' set other series properties
    End With


End Sub

Upvotes: 1

Views: 12390

Answers (1)

user2480047
user2480047

Reputation:

You are mixing up Chart and ChartObject. Here you have a sample code showing how to deal with both objects:

Dim left As Integer, top As Integer, width As Integer, height As Integer
left = 10
top = 10
width = 10
height = 10
Dim co As ChartObject
Dim c As Chart
Set co = sh1.ChartObjects.Add(left, top, width, height)
Set c = co.Chart
With c
   .ChartType = xlColumnClustered
End With

Upvotes: 2

Related Questions