Sending "Picture Links" behind charts

[Charles Crous]

Good Day,

This has stumped me. I need help layering a bubble chart over a picture (map), but the thing is the picture is linked with a formula that dynamically changes the map displayed based on a selection.

When it's a normal picture everything is fine I just use Order>Send to Back on the map, but when I tie a formula to the picture, ordering has no effect. What do I do?

Thank you,

C

 

[Mohammad Basem]

Try,

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]ActiveSheet.Shapes("[COLOR="Red"][I]Shape Name[/I][/COLOR]").ZOrder (msoSendToBack)[/COLOR][/SIZE][/FONT]

 

[Charles Crous]

Thank you Mohammed,

Unfortunately it doesn't seem to work. Here is the code that I'm using, any thoughts?

Sub ProvincialMap()
' Changes the provincial map dispalyed based on the province selected on the national map
    
    With Sheets("Dashboard").Shapes("provMap") 'provincial map
        .DrawingObject.Formula = Range("provMapSelection").Value ' create the link to selected map range
        .ZOrder (msoSendToBack)  ' order map behind chart
    End With
    
    Range("A39").Select ' Takes cell selection out of focus

End Sub

 

[Mohammad Basem]

Could you please tell me more about your project and what are you trying to achieve? Please explain the type/values of provMap and provMapSelection.

I could not run the macro and I want to know how to setup the environment to run it.

 

[Mohammad Basem]

By the way, try the other way around

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]ActiveSheet.Shapes("[COLOR="Red"][I]Chart Name[/I][/COLOR]").ZOrder (msoBringToFront)[/COLOR][/SIZE][/FONT]

 

[Charles Crous]

Sorry for the late reply (life intervenes).

"provMap" is just the name given to the picture that I've attached a formula to. Like using the camera tool, I'm trying to display an image that lies within a certain range on a different sheet, but I want to change the image that is displayed based on a selection.

This is where provMapSelection comes into play. I've created named ranges for the cells behind my different images. And based on a dropdown selecton a value between 1 and 9 is produced. This cell value (in say B1) is called myLastClick. Now in a different cell (say B2), I've used the Choose formula to select from a list of values, so provMapSelection (a named range with the range of B2) =CHOOSE(myLastClick, "ECMap", "FSMap", "GTMap", "KZMap", "MPMap", "NCMap", "NWMap", "LPMap", "WCMap").

So the provMap picture element is assigned the value of provMapSelection, which point to a range specified by a dropdown selection. This working perfectly well, but here's rub - since assigining the picture the value of provMapSelection, the Order ability doesn't want to take and I'm not able to overlay the chart over the picture.

I hope this is clearer.

 

[Mohammad Basem]

Please see this file that I have worked on based on your explanation. I did not encounter any problem regarding changes in pictures order and I did not need any macro to fix any problem.

Hope this will help.

 

[Charles Crous]

Excellent job on recreating the scenario. Sorry, I should have provided it to you!

Well, then I'm stumped, because on my side the picture is still in front of the chart, no matter what I do. Could it be a pre-set setting? Are you using Excel 2007 as well?

Thank you for your persistence!

 

[Mohammad Basem]

Yes, the order of provMap should be preset to set Send to Back and this need to be done once. I am using Excel 2010 (almost same as 2007).