Disable marker shadow on VBA-generated Excel graphs

Question

I am porting some code that I use to create scatter diagrams in Excel from Win 7 / Excel 2010, in OS X / Excel 2011. On a Mac, data points are displayed with a shadow. I don’t need a shadow, and I can’t figure out how to get rid of it.

Using this worksheet (it just has random numbers in cells A1: B6, if you don't want to load my macro-enabled worksheet) the following code works fine, but it creates data points with shadows:

Sub plotNoShadow() Dim x As Range Dim y As Range Dim cht As ChartObject Set x = ActiveSheet.Range("A1:A6") 'haphazard numbers Set y = ActiveSheet.Range("B1:B6") Set cht = ActiveSheet.ChartObjects.Add(Left:=150, Top:=50, Width:=200, Height:=160) With cht.Chart .ChartType = xlXYScatter .SeriesCollection.NewSeries With .SeriesCollection(1) .XValues = x .Values = y .Format.Shadow.Visible = msoFalse 'This seems to parse, but have no effect End With .SetElement (msoElementLegendNone) .SetElement (msoElementPrimaryValueGridLinesNone) End With End Sub 

Can someone explain to me:

• How to change this code to remove shadows, and

• How does this code work, but setting SeriesCollection (1) .Format.Shadow.Visible for msoFalse allows you to work without any visible effect?

In the comment example below, here the macro scrolling on the left indicates that the shadow is on, then off in the middle, and the shadow is on the right. For clarity, I edited the macro to remove the legend and grid lines. It appears that the macro output has less shadow than the shadow state, but more shadow than the shadow shutdown state.

Answer 1

Some default graphics styles in Excel that produce a small 3D effect also have a slight shadow, as mentioned earlier.

In my Excel (on Windows 7), the default chart style is 2, so there is no shadow or 3D effect. I suspect that on Mac the default chart style is different.

To fix this, you can set the chart style in your code:

 With cht.Chart .ChartType = xlXYScatter .ChartStyle = 2 ..... 

In Excel, ChartStyle settings have the ability to change all aspects of the appearance of the chart, including the appearance of the marker. The only thing MarkStyle sets is the shape of the marker. All other aspects of the appearance of the marker are overridden when the ChartStyle changes.

EDIT

The above comments are still mostly true, but I found a way to turn off the shadow. Like many things with Excel, it is not as easy as you think. Setting the shadow visibility property does not affect the code (for any reason), so you need to set the shadow type to "No shadow."

 Sub plotNoShadow() Dim x As Range Dim y As Range Dim cht As ChartObject Set x = ActiveSheet.Range("A1:A6") 'haphazard numbers Set y = ActiveSheet.Range("B1:B6") Set cht = ActiveSheet.ChartObjects.Add(Left:=150, Top:=50, Width:=200, Height:=160) With cht.Chart .ChartType = xlXYScatter .ChartStyle = 26 'Something 3D with a default shadow. This line can be left out. .SeriesCollection.NewSeries With .SeriesCollection(1) .XValues = x .Values = y .Format.Shadow.Type = msoShadow30 'This is the code for an inner shadow End With .SetElement (msoElementLegendNone) .SetElement (msoElementPrimaryValueGridLinesNone) End With End Sub 

EDIT Again

Actually, msoShadow30 is an “inner shadow” style and may look strange depending on your marker style. msoShadow41 is the closest to No Shadow I could find. This is actually the code for the shadow below, but by default it is too weak to see. If it appears, the color can always be changed so that it disappears.

Or even better, set the transparency to 1 (fully transparent):

 .Format.Shadow.Transparency = 1.0 'Fully transparent 

Answer 2

The shadow that you see is not really a shadow. I mean, by default, the marker looks without a shadow.

Unfortunately, there is nothing you can do. Take a look below and you’ll understand what I mean.

Snapshot:

Alternative:

However, you can play with the size of the marker to minimize this effect. Try this code.

 Sub plotNoShadow() Dim x As Range Dim y As Range Dim cht As ChartObject Set x = ActiveSheet.Range("A1:A6") 'haphazard numbers Set y = ActiveSheet.Range("B1:B6") Set cht = ActiveSheet.ChartObjects.Add(Left:=150, Top:=50, Width:=200, Height:=160) With cht.Chart .ChartType = xlXYScatter .SeriesCollection.NewSeries With .SeriesCollection(1) .MarkerStyle = 2 .MarkerSize = 7 .XValues = x .Values = y .Format.Shadow.Visible = msoFalse 'This seems to parse, but have no effect End With End With End Sub 

Answer 3

This free formatting in Mac Excel graphics made it very difficult to convert my many Mac programs. Fortunately, I discovered:

 ActiveChart.ChartStyle = 2 

which applies standard formats without shadows, gradients and glow. No need to go back and reformat every little thing in every series on the chart.

Answer 4

Click on the data series, go to the "Data format" section. In the Shadow section, the shadow box is likely to be unchecked, right? Check this. This activates the edit controls at the bottom. Then set the transparency to 100%. And you can also set the “color” to white.

Answer 5

You can kill the shadows using the legend diagram:

cht.Chart.Legend.LegendEntries (1) .LegendKey.Shadow = False

Answer 6

This naughty chart behavior will not tolerate! Desist shadowy series ...

 With .Shadow .ForeColor.RGB = &HFFFFFF .Visible = msoFalse End With