Chart Object set Marker type and color

[sanjee75]

Does Chart object has the option to set the color and image type of the marker

thanks much!

[Bob Phillips]

Of course



With ActiveChart.SeriesCollection(2)
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = 3
.MarkerStyle = xlX
.Smooth = False
.MarkerSize = 11
.Shadow = False
End With

[DougM]

Hi

I am trying to do a similar thing but specifically I am trying to set the shadow on the marker.

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 (i.e. Using the Excel Interface not VBA) and select "Format Data Series", select "Shadow" then 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.

I have also attached a sample spreadsheet. In the first Chart I have manually applied the shadow as described above, and in the second chart the VBA code applies the shadow.

Any help is 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 colour 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

[Bob Phillips]

ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(2).Format.Shadow.Type = msoShadow38


Type can be msoShadow1-43

[DougM]

Thanks xld, that worked perfectly. Should have looked a bit further but sometimes you just cant see whats directly in front of you. Cheers.