How can I programatically format just the markers in a chart series, but leave the line alone?

[Scotty81]

Hello,

I have a single data series in a simple x-y chart and I recorded some VBA code to format the data series line, its marker fill and marker border. What I've found is that when I just change the marker border (and record those actions via the Macro Recorder), what gets recorded actually works on both the marker and the data series line.

So, I want to have the line connecting the data points to be invisible, but I want the marker border to be visible and make it a specific color. When I execute the code, it makes both the marker and the line the same color. If I end my code with a command to just make the line invisible, it makes both the line and the marker border invisible as well.

Here's a snippet of the macro that got recorded:
ActiveChart.FullSeriesCollection(1).Select
Application.CommandBars("Format Object").Visible = False
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 112, 192)
End With

This is a particularly vexing issue and I suspect that the proper way to make these changes is to NOT use the macro recorder, but rather use whatever Excel VBA commands were designed to specifically work on the markers themselves. However, I am not familiar with the particular commands that do just that.

Does anyone have any suggestions?

Thanks,
Scotty81

 

[Domenic]

Try to adopt the following code...

[color=darkblue]With[/color] ActiveChart.FullSeriesCollection(1)
    .Format.Line.Visible = msoTrue
    .Format.Line.ForeColor.RGB = RGB(0, 112, 192)
    .MarkerStyle = xlMarkerStyleTriangle
    .MarkerSize = 8
    .MarkerBackgroundColor = vbYellow [color=green]'marker fill[/color]
    .MarkerForegroundColor = vbRed 'marker border
[color=darkblue]End[/color] [color=darkblue]With[/color]

Hope this helps!

 

[Scotty81]

Hi Domenic,

That gets it close. With that code, I have more granular control over the markers. But, what I did want was the line to be invisible. If I add the following line:

.Format.Line.Visible = msoFalse

just before the end of the code, it makes both the line AND the marker invisible. I suppose I could figure out what RGB corresponds to white, but I would like the line to not show at all rather than have the line be white and be displayed on top of whatever other series I have out there.

Does that make sense?

Thanks,
Scotty81

 

[Scotty81]

I think I found a solution to my own issue. If I use Domenic's code example above, and then just add this line just before the end of the snippet, I will have granular control over the marker shapes, fills and color, but I will have an invisible line.

.Border.LineStyle = xlNone

Scotty81

 

[Domenic]

That seems to work. Although, instead of setting the Visible property to msoFalse at the end of the code, I think you can simply replace the first line of code with...

.Format.Line.Visible = msoFalse

 

[jochoalions]

I have run into the same problem and have struggled with it for a while. The code as suggested does not seem to work on Excel 2010. The line

    .Border.LineStyle = xlNone

seems to have the same effect as the line

   .Format.Line.Visible = msoFalse

both lines of code remove both the series line, and the marker border.

Any other recommendations?

 

[jochoalions]

For what it is worth, and for future inquiries, I am now using a simple workaround. I plot the series twice, once without markers and the line thickness I desire, and a second time without a line between points, and marker line thickness as desired. This approach works fine, even if it is not the ideal.