Changing chart series picture fill based on data point name

[stratocat]

Hi,

I have a chart in an Excel dashboard with data from different countries that looks like this:

Country Chart.jpg

This is a simple bar pivotchart and I've just edited each data bar with a custom picture fill of the series' corresponding flag. The flag images are stored on my hard drive as, for example, "France.gif", "UK.gif" etc.

However, when the data is manipulated and a new country is added to the data, the images all move and, for instance, Germany's flag will be on the UK data bar,UK flag will move to Italy etc. Is there any way to permanently tie a picture to a particular data series?

Some investigation has led me to see that it is the data point values that I need to change the fill picture of and I have managed to get some code together to pull the names out of each data point in the chart:

Dim ChartCountries As Variant
Dim CountryName As Variant

ChartCountries = ActiveChart.SeriesCollection(1).XValues

For Each CountryName In ChartCountries
            Msgbox CountryName
    End With
Next

This just gives me a message with each country in the chart but I want to try and then use this name to make the corresponding flag always fill the bar in the chart for that data point. Have tried:

For Each CountryName In ChartCountries
    With CountryName.Format.Fill
       .Visible = msoTrue
       .UserPicture "C:\Users\JMC\Documents\Flags\" & CountryName & ".gif"
       .TextureTile = msoFalse
    End With
Next

But I get 'object required' error as I'm not sure of the right sytax to use here. Can anyone help with some code to fill the chart bar with a picture based on the data point name which is just going to be a flag of that country?

 

[stratocat]

Friendly bump to see if anyone has any idea on this... it's driving me crazy! This blog post from Jon Peltier (VBA Conditional Formatting of Charts by Category Label - Peltier Tech Blog) is almost what I'm trying to do but instead of changing colour of series, I want to change to a picture (flag) which has the same file name (+ .gif) as the data point name... just don't know how to manipulate the code to achieve it... Any ideas?

 

[Jon Peltier]

This is one reason to avoid using variants. They are not inherently evil, as some would have you believe, but they make you lazy.

So what are the actual types of your variants? Pause the code in the middle of the For-Next loop and open the Locals window.

We learn that ChartCountries is a variant array, dimmed from 1 to the number of values in the array of XValues. And we learn that CountryName is a string (it would be a double if XValues contained numerical values).

You can't format a string as if it were a plotted point.

So let's do this more systematically, with actual object variables that match the objects in the chart. I haven't actually tested this, but it's much closer to the syntax that will make your code work.

Dim vCountries as Variant ' use a prefix to remind yourself what it is
Dim srsCountries as Series
Dim ptCountry as Point
dim iCountry as Long
Dim sCountry as String

set srsCountries = ActiveChart.SeriesCollection(1)
vCountries = srsCountries.XValues

For iCountry = LBound(vCountries) to UBound(vCountries)
  sCountry = vCountries(iCountry)
  set ptCountry = srsCountries.Points(iCountry)
  with .ptCountry.Format.Fill
    .Visible = msoTrue '' don't know it this is really needed
    .UserPicture "C:\Users\JMC\Documents\Flags\" & sCountry & ".gif"
    .TextureTile = msoFalse '' don't know it this is really needed
  End With
Next

 

[stratocat]

Outstanding! Thanks a million Jon! This works a treat and is exactly what I was looking to do! I had no idea about the labels window but stepping through the macro, I can see it's really useful to use for understanding what's going on - definitely a good tip for the future, thanks... and big thanks again for solving my problem!

I had also read and posted on your blog as mentioned above - I see this has been referenced already through the link so hopefully can help others looking for similar things.