Error 1004 with SetBackgroundPicture

[aarbrown]

Hi all,

I have written some VBA in Access that is controlling an Excel spreadsheet I have created using OLE automation. Everything works fine, except I am having trouble getting my VBA to set the background picture of a chart on my spreadsheet. Here is the code from the subroutine that is adding the background to the chart:

Private Sub fillchart(ByVal rngDataSource As range)
Dim iDataRowsCt As Long
Dim iDataColsCt As Integer
Dim iSrsIx As Integer
Dim srsNew As Series
    With rngDataSource
        iDataRowsCt = .Rows.Count
        iDataColsCt = .Columns.Count
    End With
    With chtCTQChart
        .ChartType = xlXYScatter
        For iSrsIx = 2 To iDataRowsCt
            Set srsNew = .SeriesCollection.NewSeries
            With srsNew
                .Name = rngDataSource.Cells(iSrsIx, 1)
                .Values = rngDataSource.Cells(iSrsIx, 2)
                .XValues = rngDataSource(iSrsIx, 3)
            End With
        Next iSrsIx
        With .Axes(xlValue)
            .MaximumScale = 5
            .MinimumScale = 0
        End With
    .SetBackgroundPicture FileName:="c:\chartbackground.gif"
    End With

End Sub

 

[Andrew Poulsom]

Can you do it manually? If you record your actions what code do you get?

 

[aarbrown]

When I record a macro I am getting the following code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/12/2005 by Aaron Brown
'

'
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.ChartArea.Select
    ActiveChart.PlotArea.Select
    With Selection.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Fill.UserPicture PictureFile:="C:\newpicture.GIF"
    Selection.Fill.Visible = True
    ActiveChart.ChartArea.Select
End Sub

I tried substituting the chtCTQChart.SetBackgroundPicture with the Selection.Fill.UserPicture PictureFle:="C:\newpicture.GIF" from above, but I am recieving an error 1004 saying the chtCTQChart.PlotArea.Select Failed. Here is the code from Access:

Private Sub fillchart(ByVal rngDataSource As range)
Dim iDataRowsCt As Long
Dim iDataColsCt As Integer
Dim iSrsIx As Integer
Dim srsNew As Series
    With rngDataSource
        iDataRowsCt = .Rows.Count
        iDataColsCt = .Columns.Count
    End With
    With chtCTQChart
        .ChartType = xlXYScatter
        For iSrsIx = 2 To iDataRowsCt
            Set srsNew = .SeriesCollection.NewSeries
            With srsNew
                .Name = rngDataSource.Cells(iSrsIx, 1)
                .Values = rngDataSource.Cells(iSrsIx, 2)
                .XValues = rngDataSource(iSrsIx, 3)
            End With
        Next iSrsIx
        With .Axes(xlValue)
            .MaximumScale = 5
            .MinimumScale = 0
        End With
       .ChartArea.Select
       .PlotArea.Select
                
    End With
    With Selection.Fill
        .UserPicture PictureFile:="C:\newpicture.GIF"
        .Visible = True
    End With
End Sub

 

[aarbrown]

just bouncing this to the top

 

[Joe Was]

You need to activate or select the chart object before you can work with it:

Charts("chart1").Activate

or

Charts(1).Activate
With ActiveChart

or

Worksheets(1).ChartObjects(1).Activate

 

[aarbrown]

Thank you all very much for your help. Yes, I did find that I had to activate the chart before I could use that property. However, when I activated the chart Excel would crash if I clicked on the first worksheet for some reason. Therefore I needed to set the background picture without activating the chart. I found, thanks to the macro, a property that would allow me to do that:

PlotArea.Fill.UserPicture PictureFile:="C:\newpicture.GIF"

I added that to my code, using the chart object, and it worked perfectly!

 

[Joe Was]

Yes, Excel will do that, it lacks Inheritance: the ability for one object to modify another object directly, without some intermediary. Glad you found it.