VBA to colour a picture object

[Jonatmv]

I'm really struggling with this one, and can get several results that don't quite do what I want.

I've created a map from several smaller components. So that the background of each segment doesn't obscure the other map sections, I have coloured the background grey (RGB 200,200,200) in the png file.
In Excel, I have then reformatted the picture and set this background colour to be transparent.


What I now have is an unobscured map of overlapping sections, and I want to colour code the visible sections of each differently to identify different regions.

I can do this manually by selecting an object, going to the picture recolour options, and picking a colour. However, I want to do this from VBA as the colour will depend on a value in a cell. The record macro function cannot capture this action.

For the life of me, I cannot find the VBA code to manipulate just the foreground colour (i.e. the bit that isn't transparent). Using the .forecolor and .backcolor handles will only recolour my transparent section (i.e. the background), but it refuses to change the visible section (foreground) colour.

I can force a change using something like this:

Please Login or Register  to view this content.

but I'm restricted to the coloraccent schemes, of which there are just six pastel shades. I need the full gamut, ideally specified via RGB(x,y,z)!

Please help!

[Andy Pope]

You can not change the individual colours in pictures in the way I think you are expecting to.
With a picture you can make 1 of it's colours the transparency colour and thats it. All other formatting is done on the picture as a whole.

It may help if you post a workbook example and a more detailed explanation of want you are trying to do.

[Jonatmv]

Sample file attached.

I just want to change the green in this shape to (say) RGB(200,0,0)

[Jonatmv]

Should have said, I've saved this as an .xlsm but there is no code within it.

[Andy Pope]

Not going to work with picture.

What you need is a freeform shape. I did something years ago with the US map
http://www.andypope.info/ngs/ng12.htm

You might find this blog interesting.
http://www.clearlyandsimply.com/clea...ape-files.html

[Andy Pope]

I'm assuming the example picture was just 1 on many pictures you are piecing together? If it's just that one picture then you can do it by reversing the transparent colour and placing a shape behind that you then change the colour of.

[Jonatmv]

Yes - there are multiple picture files with transparent background.

I can get one picture to work if I invert the transparent areas (e.g. have "hollow" regions), but obviously when I then put two of these along side each other the solid white areas then obscure one another.

It's frustrating, because you can action this simply by clicking on re-colour... so it's not like Excel cannot work out what to do: it just seems that it isn't programmable.


I looked at your USA map. I have custom areas in the UK. Is there a simply way for me to convert these images in to something Excel will understand as a shape? My mapping software can export EMF and SVG files, but following the other link you sent me: the mapping software encodes the regions in stripes rather than bordered regions, so I'm not getting anywhere with that.

(Full file attached with all regions for ref.)

[Andy Pope]

One way would be to duplicate all those shapes and manually set the recolor values.
Then you can hide all but one version of the shape and use code to toggle visible state of each image.

[Jonatmv]

One way would be to duplicate all those shapes and manually set the recolor values.
Then you can hide all but one version of the shape and use code to toggle visible state of each image.

Yes; I think this may be my best option. Obviously a bit limiting ... but better than nowt.

Thanks Andy.