Changing line transparency of a series without affecting marker transparency in Excel VBA

Question

I am writing a macro in VBA for excel in which I would like to change the transparency of the lines connecting markers in a series but leave the transparency of the markers in the series the same.

To specify: the chart is a scatter plot. I would like the markers for a series to be opaque/zero transparency and for the lines in the series to be 75% transparent.

I have adjsuted the transparency of the lines by using myseries.format.line.transparency = 0.75 but this changes the marker transparency as well.

does anyone know of a way I can change the transparency of the two separately? I imagine there is a member/property to do what I want, but I cannot find it.

thanks in advance for any help!

Answer 1

This answer isn't going to make you very happy.

I've looked into this before and the information i've gotten is that this simply isn't a parameter that you can specify through VBA. It looks like you can access marker style, size, background color and foreground color, and that's about it.

Maybe MS didn't think anyone would ever want to mess with that.

One thing you could try is applying a custom chart format, but if you have variable numbers and/or orders of series then that may not work.

 mychart.ApplyChartTemplate ("filepath\filename.crtx")

Something like that, where mychart is already set equal to the chart you want to format.

Again, maybe not of any use to you, best i could think of.

Answer 2

You guys didn't dig hard enough.

SeriesCollection(i).Format.Line.Transparency

will work if the SeriesObject is in a certain state. The original 'automatic' line style state prevents this vba from doing anything at first, but if you simply precede it by setting certain other properties on the line format first, then the transparency will take. The following worked for me:

For Each obj In myChart.SeriesCollection
    obj.Format.Line.DashStyle = 1
    obj.Format.Line.Transparency = 0.65
Next obj

DashStyle = 1 sets the line style to 'Solid' (as opposed to dashed, dotted, etc.) and has the side effect of freeing up the series format to have the transparency set. I don't know for sure why it works, but it does.

Answer 3

Sorry, my mistake. I read the question slightly wrong.

I find that if I start the line with no markers, then turning transparency separately doesn't turn the markers on and off.

Answer 4

Try this:

activechart.SeriesCollection(1).format.fill.transparency=0.5