Turn off marker shadow on vba-generated Excel plots

calendar_today Asked Apr 16, 2012
thumb_up 5 upvotes
history Updated April 16, 2026

Question posted 2012 · +6 upvotes

I’m porting some code I use to generate scatter plots in Excel from Win 7 / Excel 2010, to OS X / Excel 2011. On the Mac, the data points show up with a shadow. I don’t want the 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 download my macro-enabled worksheet) the following code runs fine, but produces 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 anyone explain to me:

  1. How to modify this code to remove the shadows, and

  2. How it is that this code runs, but setting the SeriesCollection(1).Format.Shadow.Visible to msoFalse manages to run without having any apparent effect?

Per the comment thread below, here’s a screencap of the macro output on the left, points with shadow turned on then off in the middle, and shadows turned on on the right. I edited the macro to remove legend and gridlines, for clarity. It looks like the macro output has less shadow than the ‘shadow on’ state, but more shadow than the ‘shadow off’ state.

Screencap to illustrate the three states of shadow existence

Accepted answer +5 upvotes

Several of the default Chart Styles in Excel that produce a slight 3D effect also have a minor drop-shadow as pointed out earlier.

On my Excel (in Windows 7) the default chart style is 2, so no shadow or 3D effect appears. I suspect that on the 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, the ChartStyle settings have the ability to modify all aspects of the appearance of the chart, including the look of the Marker. The only thing MarkStyle sets is the shape of the Marker. All of the other appearance aspects of the Marker are overridden when the ChartStyle is changed.

EDIT

The above comments are still basically true, but I have found a way to shut off the shadow. Like many things with Excel, it is not as easy as you would think. Setting the visibility property of the shadow has no effect if done in code (for whatever 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 “internal shadow” style and may look strange depending on your marker style. msoShadow41 is the closest thing to “No Shadow” that I have been able to find. It’s actually the code for shadow below, but by default, it is too faint to see. If it does show up, the color can always be changed to make it disappear.

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

.Format.Shadow.Transparency = 1.0 'Fully transparent

3 code variants in this answer

  • Variant 1 — 4 lines, starts with With cht.Chart
  • Variant 2 — 25 lines, starts with Sub plotNoShadow()
  • Variant 3 — 1 lines, starts with .Format.Shadow.Transparency = 1.0 'Fully transparent

Top excel-vba Q&A (6)

+5 upvotes ranks this answer #124 out of 136 excel-vba solutions on this site .
excel-vba