Colour Key (like a chart legend) 1

[Stretchwickster]

I have written some VBA code to plot a column chart. I then run a routine that colours each one of these columns in the chart according to their values. Example: 15 columns have been plotted, my routine has been executed. Of these 15 columns, 3 are blue, 5 are purple and 7 are yellow.

What I want to be able to do is show a key (exactly like a legend) of these 3 colours to explain what each colour means? I have added a legend but I can't seem to edit the values inside it - I can remove entries but I can't add any. Is it best not to use a legend in this case? Clive

 

[Stretchwickster]

Forgot to mention - the Legend currently contains every x-axis value when I simply just want to define what each of the 3 different colours represent. Can anyone help? Clive

 

[RobBroekhuis]

You can go two ways. I've had to make custom legend boxes before when I wanted to use features Excel doesn't support, such as superscripts etc in the legend. That's a lot of work, though - if you can make do with Excel's standard legends, that's the way to go.
It wouldn't be difficult to write code to remove legend entries that refer to duplicate colors. The remaining legend entry would have as legend text the name of the corresponding series, which you could also set programmatically. I think this would work for you. If you need help, let us know.

Rob

 

[Stretchwickster]

Rob,

I would prefer not to go down the custom legend box road!
I've had a good look in the help and it says it's not possible to change the text of a legend entry. Also, the legend entries are based on x-axis labels.

You said:

>The remaining legend entry would have as legend text the >name of the corresponding series...

The way I have it setup means that even after I have deleted all but the last legend entry it still shows one of the x-axis labels rather than the series name.

Basically, what I need to do is have 3 legend entries representing the 3 colours on my chart and be able to name them "Over", "Under" and "Normal". Can anyone suggest any code - cos it seems like it's not possible (after looking at the help)? Clive

 

[RobBroekhuis]

How do you have your chart set up (how many series, etc)? With a simple column chart, you have just ONE legend entry - the series name. It sounds like you have used a separate series for each column (i.e., you chose the "data in rows" option when your data is in columns, or vice versa). You CAN create (with some trickery) extra legend entries - by defining an additional series without data. Post some code showing how you've assigned the colors - that will tell us how your chart is configured.
Rob

 

[Stretchwickster]

I have one single series of ChartType=xlColumnClustered. I use SetSourceData ... PlotBy:=xlColumns. This results in all columns being a standard blue colour. I then use the following code to change colours:

  Dim yValues
  Dim i As Integer
  
  yValues = ActiveChart.SeriesCollection(1).Values
  For i = 1 To UBound(yValues)
    If yValues(i) > UpperValue Then
      ActiveChart.SeriesCollection(1).Points(i).Interior.ColorIndex = 3 // red
    ElseIf yValues(i) < LowerValue) Then
      ActiveChart.SeriesCollection(1).Points(i).Interior.ColorIndex = 6 // yellow
    Else
      ActiveChart.SeriesCollection(1).Points(i).Interior.ColorIndex = 4 // green
    End If
  Next i

Clive

 

[Stretchwickster]

Excuse the comments - force of habit from other languages! // should read ' to represent comments. Clive

 

[RobBroekhuis]

Yup, your chart seems to be properly configured. I'm puzzled that you get as many legend entries as you have columns; when I create a new ColumnClustered chart, I have just one legend entry - for the entire series, with as text the name of the series. Since I usually work with XY charts, maybe I'm not familiar with the legend options for column charts. Your data range consists of two columns? What happens if you delete the legend and re-insert it?
Rob

 

[Stretchwickster]

Rob,

Thanks once again for your patience! My data range does have 2 columns. I tried deleting the legend and re-inserting it to no effect.

But...

I realised from a bit of trial and error that I wasn't adding a series to the graph before adding the data - this probably makes all the difference doesn't it? Cos when I add 2 new series the legend reverts to representing each series as you originally said.

What I have been able to do is name the 3 series "Over", "Under" and "Normal" respectively. I also managed to change the colour to match those in my above code. But there's just one more annoyance: when I add 2 new series it gives each series a default column valued at 1. I have set this to 0. The problem is that although I've effectively hidden the 2 pseudo-series there is a space for them on the graph which means the columns of the series that is visible are pushed across to the left each time. Clive

 

[RobBroekhuis]

Yes, I guess that would happen. Empty series in an XY chart do absolutely nothing (besides show up on the legend, but I'd guess you may not be able to prevent them from exerting their influence on the column chart. You may be back to creating the custom legend. Not really all that difficult, just more code to write. Since it sounds like you're creating the same chart with different data, why not just draw one using Excel's drawing toolbar while leaving the macro recorder on, and paste that code into your macro? It won't be pretty (and you can do some cleaning up), but it will get the job done.
Rob

 

[RobBroekhuis]

Playing around, I came across a possible solution. Try:

ActiveChart.ChartGroups(1).Overlap = 100

Rob