VBA dynamic images for plot area in radar chart

[oCoCarbon]

I'm trying to paste an image as a background to a radar chart. The objective is to let the user choose from a number of background images to suit what is being shown on the radar chart.

I've tried recording a macro but it skips the vital step of pasting the image from the clipboard. (This isn't how I'll actually use the code but I've left the macro as recorded for completeness).

    ActiveSheet.Shapes.Range(Array("Picture 21")).Select
    Selection.Copy
    ActiveSheet.Previous.Select
    ActiveSheet.ChartObjects("Radar").Activate
    ActiveChart.PlotArea.Select
    'this is where the formatting happens but the macro hasn't recorded it

Can anybody help fill the gap?

An alternative would be to have a stack of images behind a transparent chart that get made visible as required. Code for making sure they line up exactly with the plot area would be my difficulty there though.

 

[MickG]

You can get i pocture from a file, like this:-

[COLOR=navy]Sub[/COLOR] MG06May02
[COLOR=navy]Dim[/COLOR] Pic [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
ActiveSheet.ChartObjects("Chart 2").Activate
 Pic = "C:\Documents and Settings\test\My Documents\My Pictures\MyPic.jpg"
[COLOR=navy]With[/COLOR] ActiveChart.ChartArea
    .Fill.UserPicture PictureFile:=Pic
    .Fill.Visible = True
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[oCoCarbon]

Two problems with that. One is that it's the PlotArea I want to fill. Two is that this needs to be portable without having to distribute additional files.

Thanks anyway.

 

[MickG]

If you place your chart on a Plain rectangular shape, place you pictures on top of the shape then make the chart transparent and place it on top of the pictures. Then "Group" all the items , You can then show the one picture you want in the plot area, by using some code similar to below.
Its a bit fiddly,but it does work
One you find all the Shape Names you can record a Macro to "Group" them.

Dim shp As Shape
For Each shp In ActiveSheet.Shapes("Group 1054").GroupItems
  If Left(shp.Name, 7) = "Picture" Then shp.Visible = False
           If shp.Name = "Picture 1052" Then shp.Visible = True
        Next shp

Regards Mick

 

[hstaubyn]

Does anyone know the answer to the question being asked?

ActiveSheet.Shapes.Range(Array("Picture 21")).Select
Selection.Copy
ActiveSheet.Previous.Select
ActiveSheet.ChartObjects("Radar").Activate
ActiveChart.PlotArea.Select
'this is where the formatting happens but the macro hasn't recorded it

I am trying to do the same thing. I have two pictures in a worksheet in the same workbook as the chart. Depending on a certain value, I would like to copy one of the pictures and insert it as the plot area background. You can do this manually, but when 'recording macro' to get the code, the bit where you click on 'From Clipboard' to actually insert the picture doesn't get recorded.

Does anyone know how to accomplish this in VBA? I too would like to avoid relying on other files outside the excel file being worked on and would also like to avoid making the chart a picture, transparent etc.

Thanks,
Henry