AddChart in a sheet that is not active/selected (VBA Excel)

Question

I'm trying to create a new chart with the following code:

Sheet1.Range("C1:C21").TextToColumns Destination:=Sheet1.Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
Sheet1.Select
Set classificacio = Sheet1.Shapes.AddChart2(Style:=-1, XlChartType:=xlBarStacked, Left:=Sheet1.Cells(1, 1).Left, Top:=Sheet1.Cells(1, 1).Top, Width:=Sheet1.Range(Cells(1, 1), Cells(15, 8)).Width, Height:=Sheet1.Range(Cells(1, 1), Cells(20, 8)).Height, NewLayout:=True).Chart
classificacio.SetSourceData Source:=Range(Sheet1.Cells(11, 2), Sheet1.Cells(20, 3))
classificacio.HasTitle = False

It works. However, it doesn't work when I delete Sheet1.Select because the code starts when Sheet3 is active. It seems that Set classificacio = Sheet1.Shapes... only makes sense when Sheet1 is the ActiveSheet.

How could I create this chart avoiding the selection of Sheet1, where the chart is created, and keeping always Sheet3 as the ActiveSheet?

score 1 · Accepted Answer · answered Apr 12 '19 at 18:02

You are getting that error because the Cells object are not fully qualified. If you do not qualify them, then they will refer to the active sheet.

Change Sheet1.Range(Cells(1, 1), Cells(15, 8)) to Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(15, 8)). Same with Sheet1.Range(Cells(1, 1), Cells(20, 8))

Or better still, Put then in a With-End With Block (Untested).

With Sheet1
    .Range("C1:C21").TextToColumns Destination:=.Range("C1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

    Set classificacio = .Shapes.AddChart2(Style:=-1, XlChartType:=xlBarStacked, _
                                          Left:=.Cells(1, 1).Left, _
                                          Top:=.Cells(1, 1).Top, _
                                          Width:=.Range(.Cells(1, 1), .Cells(15, 8)).Width, _
                                          Height:=.Range(.Cells(1, 1), .Cells(20, 8)).Height, _
                                          NewLayout:=True).Chart

    classificacio.SetSourceData Source:=Range(.Cells(11, 2), .Cells(20, 3))
    classificacio.HasTitle = False
End With

Thank you! It works now. What are the advantages of using With - End With? Not only in this case, but also in any other example. — Abelois, Apr 12 '19 at 18:07
In this case, you do not have to write `Sheet1` again and again. I would recommend reading about **With Statement** [Here](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/with-statement) — Siddharth Rout, Apr 12 '19 at 18:09

AddChart in a sheet that is not active/selected (VBA Excel)

1 Answers1