Using Transparent Colors in a Radar Chart

[Marc Davidson]

HI,

I was wondering if anyone knew if it is possible to add
tranparency to a color in a radar chart. I have created a
radar chart that has 5 points and two colors. the radar
chart will create two polygons which overlap. If the
colors are not transparent, which appears to be the only
option, sometimes you cannot see what is behind one of the
polygons. Now this is natural but it would be nice if you
could see what's behind for reference.

I have tried to change the color attributes in the
gradient window but the transparency change bars do not
work.

Any suggestions?

Thanks,
Marc

 

[John Walkenbach]

I don't think it's possible. I've documented a way to make column and bar
chart series transparent:

http://j-walk.com/ss/excel/usertips/tip071.htm

But it doesn't seem to work for radar charts.

Maybe you should switch to a Macintosh?

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss

 

[Jon Peltier]

Come on, John, anything's possible. Especially if you're not too picky.

This little macro draws freeform shapes on the chart that match each of
its radar series. Once these are drawn, you can select each one and
apply the line and fill color you want, and check the semitransparent
option. I think I fixed all the word wraps, but just watch out.

Sub DrawAShapeOnRadarChart()
Dim cht As Chart
Dim srs As Series
Dim Npts As Integer, Ipts As Integer
Dim myShape As Shape
Dim Xnode As Double, Ynode As Double
Dim Rmax As Double, Rmin As Double
Dim Xleft As Double, Ytop As Double
Dim Xwidth As Double, Yheight As Double
Dim dPI As Double

Set cht = ActiveChart
Xleft = cht.PlotArea.InsideLeft
Xwidth = cht.PlotArea.InsideWidth
Ytop = cht.PlotArea.InsideTop
Yheight = cht.PlotArea.InsideHeight
Rmax = cht.Axes(2).MaximumScale
Rmin = cht.Axes(2).MinimumScale
dPI = WorksheetFunction.Pi()

For Each srs In cht.SeriesCollection

Select Case srs.ChartType
Case xlRadar, xlRadarFilled, xlRadarMarkers

Npts = srs.Points.Count

Xnode = Xleft + Xwidth / 2 * _
(1 + (srs.Values(Npts) - Rmin) / (Rmax - Rmin) _
* Sin(2 * dPI * (Npts - 1) / Npts))

Ynode = Ytop + Yheight / 2 * _
(1 - (srs.Values(Npts) - Rmin) / (Rmax - Rmin) _
* Cos(2 * dPI * (Npts - 1) / Npts))

With cht.Shapes.BuildFreeform _
(msoEditingAuto, Xnode, Ynode)
For Ipts = 1 To Npts

Xnode = Xleft + Xwidth / 2 * _
(1 + (srs.Values(Ipts) - Rmin) / (Rmax - Rmin) _
* Sin(2 * dPI * (Ipts - 1) / Npts))

Ynode = Ytop + Yheight / 2 * _
(1 - (srs.Values(Ipts) - Rmin) / (Rmax - Rmin) _
* Cos(2 * dPI * (Ipts - 1) / Npts))

.AddNodes msoSegmentLine, msoEditingAuto, _
Xnode, Ynode
Next
Set myShape = .ConvertToShape
End With

With myShape
.Fill.ForeColor.SchemeColor = 13 ' YELLOW
.Line.ForeColor.SchemeColor = 12 ' BLUE
End With
End Select
Next

End Sub


- Jon

 

[Jon Peltier]

For the code I provided, I should point out that you can't see the
underlying transparent fill colors (at least not in Excel 97), but you
can see the lines and gridlines. If you have two series, you can skip
making the bottom one transparent.

- Jon

 

[Andy Pope]

Hi Jon,

You beat me to it!

The macro creates freeform shapes and uses these as a custom marker.
(http://www.andypope.info/ngs/ng23.htm)

Come on, John, anything's possible. Especially if you're not too picky.

This little macro draws freeform shapes on the chart that match each of
its radar series. Once these are drawn, you can select each one and
apply the line and fill color you want, and check the semitransparent
option. I think I fixed all the word wraps, but just watch out.

Sub DrawAShapeOnRadarChart()
Dim cht As Chart
Dim srs As Series
Dim Npts As Integer, Ipts As Integer
Dim myShape As Shape
Dim Xnode As Double, Ynode As Double
Dim Rmax As Double, Rmin As Double
Dim Xleft As Double, Ytop As Double
Dim Xwidth As Double, Yheight As Double
Dim dPI As Double

Set cht = ActiveChart
Xleft = cht.PlotArea.InsideLeft
Xwidth = cht.PlotArea.InsideWidth
Ytop = cht.PlotArea.InsideTop
Yheight = cht.PlotArea.InsideHeight
Rmax = cht.Axes(2).MaximumScale
Rmin = cht.Axes(2).MinimumScale
dPI = WorksheetFunction.Pi()

For Each srs In cht.SeriesCollection

Select Case srs.ChartType
Case xlRadar, xlRadarFilled, xlRadarMarkers

Npts = srs.Points.Count

Xnode = Xleft + Xwidth / 2 * _
(1 + (srs.Values(Npts) - Rmin) / (Rmax - Rmin) _
* Sin(2 * dPI * (Npts - 1) / Npts))

Ynode = Ytop + Yheight / 2 * _
(1 - (srs.Values(Npts) - Rmin) / (Rmax - Rmin) _
* Cos(2 * dPI * (Npts - 1) / Npts))

With cht.Shapes.BuildFreeform _
(msoEditingAuto, Xnode, Ynode)
For Ipts = 1 To Npts

Xnode = Xleft + Xwidth / 2 * _
(1 + (srs.Values(Ipts) - Rmin) / (Rmax - Rmin) _
* Sin(2 * dPI * (Ipts - 1) / Npts))

Ynode = Ytop + Yheight / 2 * _
(1 - (srs.Values(Ipts) - Rmin) / (Rmax - Rmin) _
* Cos(2 * dPI * (Ipts - 1) / Npts))

.AddNodes msoSegmentLine, msoEditingAuto, _
Xnode, Ynode
Next
Set myShape = .ConvertToShape
End With

With myShape
.Fill.ForeColor.SchemeColor = 13 ' YELLOW
.Line.ForeColor.SchemeColor = 12 ' BLUE
End With
End Select
Next

End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

--

Cheers
Andy

http://www.andypope.info

 

[Jon Peltier]

Andy -

I can't let you have all the fun!

- Jon

Hi Jon,

You beat me to it!

The macro creates freeform shapes and uses these as a custom marker.
(http://www.andypope.info/ngs/ng23.htm)