Setting Shadow Affect on the Markers in a Chart

**DougM** · 03-19-2010, 01:55 AM

I am actually doing this from Access but in order to simply and try to isolate the issue I have put it into a simple macro in excel.

I have a chart on a worksheet on which I want to re-format the default format of the series line & markers. If I right click on the series line interactively and select "Format Data Series", select "Shadow" the select a "Preset" shadow, it applies the shadow to the Marker 'which is exactly what I want.

However, when I try to do this from VBA, it applies the shadow formatting to the lines that join the markers rather than the markers themselvs. Funny thing is, I cant actually find a way to do apply shadows to the lines interactively, not that I want to.

My code is below and I have documented it as much as I can to explain what I am doing and an alternative that I have tried. If anyone can figure out how to get the shadow affects to work on the Markers rather than the lines through VBA it would be much appreciated.

'=================================================================

Dim excChart As Excel.Chart
Dim excChartSeries As Excel.Series
Dim excPoint As Excel.Point

Set excChart = ActiveSheet.ChartObjects("NameOfYourChart").Chart
Set excChartSeries = excChart.SeriesCollection(2) 'My chart has two data series, the first being a bar graph and the second being a line graph.

With excChartSeries

    'The next 2 lines correctly set the size and style of the Markers
    .MarkerBackgroundColor = RGB(255, 255, 255) 
    .MarkerForegroundColor = RGB(0, 176, 80) 
    
    'The affect of the next 3 lines is that it applies the shadow formatting to the lines that
    'join the markers rather than to the markers themselves.

    .Shadow = True
    .Format.Shadow.Blur = 5
    .Format.Shadow.ForeColor.RGB = RGB(0, 176, 80)

    'The next 2 lines correctly set the size and style of the Markers
    .MarkerSize = 12
    .MarkerStyle = xlMarkerStyleCircle

    'The above didnt work so I tried iterating through the points collection and setting 
    'these properties on each individual Marker or Point.
    'All that happens is that for each Marker it sets the shadow on the lines that join all the 
    'markers again, rather than on the individual marker itself.

    For Each excPoint In .Points
        .Shadow = True
        .MarkerBackgroundColor = RGB(255, 255, 255)
        .MarkerForegroundColor = RGB(0, 176, 80)
        .Format.Shadow.Blur = 5 
        .Format.Shadow.ForeColor.RGB = RGB(0, 176, 80) 
        .MarkerSize = 12
        .MarkerStyle = xlMarkerStyleCircle
    Next

End With

Any help is appreciated,
Cheers

**Andy Pope** · 03-19-2010, 04:47 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Can you post example of a chart where just the data marks have shadow.

**DougM** · 03-21-2010, 06:50 PM

Hi Andy

I have added the tags, sorry, this is my first post here.

I have attached an example as requested. The shadow to the markers has been applied in "Chart 1" as described previously by right clicking on the line series and setting the shadow options. This is the affect I wish to achieve via code. I would prefer to simply save this formatting as a template and apply the template to chart but it seems that templates only save very limited formatting so I need to do this via code.

If however you attempt to do this in code, it sets the shadow on the lines instead. I have included the code previously posted in the attached file so you can run it from there. It applies the shadow formatting to the second chart on the worksheet "Chart 2".

See how you go.

Cheers
Doug

P.S. I'm probably on the other side of the world to you so any response are likely to be posted overnight to you.

**Andy Pope** · 03-22-2010, 04:42 AM

I have had a quick look and there does not appear to be anything in the Object Model to control specifically the marker shadow.

If I get chance I will investigate further.

**DougM** · 03-23-2010, 06:12 PM

Hi Andy

Just letting you know that I found a solution.
Its .SeriesCollection(2).Format.Shadow.Type = msoShadow38
Obviously there are many "Types" to choose from but this works on the Markers not the lines.

Cheers
Doug

**Andy Pope** · 03-24-2010, 04:57 AM

Thanks for posting back the solution.

Did you find some information explaining the various shadow types or was it just hit and miss?

**DougM** · 03-24-2010, 06:19 PM

Hi Andy

It was just hit & miss.

The solution was posted to me on another forum and it just so happened that msoShadow38 which was in the solution post was damn close to the shadow affect I was looking for anyway. So it was just a case of trial and error either side of msoShadow38 till the shadow setting was going in the right direction, ultimately to msoShadow36 which was the effect I was after.

I was hoping there might be some logic to the msoShadow types available, but the numbers dont even match the "Preset" list available when you right-click and Format the series and then select Shadow. In there you only have a choice of about 23 preset shadow types, but via the object model you have up to 40!

Cheers
Doug