CODEWITHSUNDEEP
excelimagevbacharts
gde211
gde211

Reputation: 15

delete chart picture without deleting chart

In trying to export a range as picture to use on userform I have the following:

FolderPath = ThisWorkbook.Path & "\dbfiles"
If ComboBox3.Value = "Cube" Then
    With Worksheets("Form")
        Set r = .Range("AE21:AM52")
        r.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
        Set chtO = .ChartObjects("Chart 4")
    End With
    With chtO.Chart

        .Paste
        .Export Filename:=FolderPath & "\temppic.bmp", FilterName:="BMP"
    End With

The problem is that when the code runs and there is already a picture in the chart area, the 'new paste' is sized differently and does not fill the chart area. I need this to happen so the picture lines up with other objects on my userform. I've found that if I manually delete the picture but leave the chart (click, press delete), everything works fine. So my question is, how do you do this pragmatically? I've tried .delete where the gap is to no avail. (Method delete of object_chart failed) Thanks in advance.

Upvotes: 0

Views: 380

Answers (1)

Tim Williams
Tim Williams

Reputation: 166825

With chtO.Chart
    Do While .Shapes.Count > 0
        .Shapes(1).Delete
    Loop
    .Paste
    .Export Filename:=FolderPath & "\temppic.bmp", FilterName:="BMP"
End With

Upvotes: 1

Related Questions