XY Scatter Legend when Edit Individual Markers

[John]

Usually, I have to chart alot of data. I cannot really use color to
distinguish the line and the line styles leave alot to be desired when the
points are close together. Therefore, I use the symbols to better
distinguish the data series. However, with alot of data the symbols make the
lines just appear fat, again making it difficult to distinguish the line.
Therefore, I loop through the points and set the individual point markers at
a different frequency for each series. This works great. However, the
legend doesn't display the markers. The first point on every data series
always has a marker. Is there a way to change what the Legend displays wrt
symbols?

Because I do the symbol addition programmatically the first data series on a
chart always has the same look. However, when there is only one series on
the chart the legend changes to show a list of xlCategory values for the
points with the line and symbol style next to them. I need the legend
because the titles are generic for a group of about 50 charts. Is there a
way to prevent this behavior?

XMGR is sooooo much easier at plotting alot of data.

Thanks,
John

 

[Bernard Liengme]

The solution is to use another data series with sparse data.
Lets say your x-values are in A1:A1000, y-values in B1:B1000
In C1 enter =IF(MOD(ROW(),50)=1, B1, NA()); copy down to C1000
This displays column B values fro every 50th row and #N/A for those in
between.
Select A1:C1000 and make plot
Format B values as Line only and C values as markers only
Remove legend for B series
best wishes

 

[Jon Peltier]

John -

Instead of programmatically adding markers selectively to a series with lines and no
markers, programmatically remove markers from a series with lines and markers. The
legend retains the format of the series, which is line with marker.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[John]

Thanks for the response.

The solution is too bulky for my needs. I cannot afford to do it
programatically for as many charts I could have.

John

 

[John]

Thanks for the response.

I think I used to do it that way years ago. I chose the current because it
is faster, touching the fewest number of data points, which could ba a
thousand or more per series (up to 12 per chart) per chart (maybe 50 charts).
However, if this is the best solution that I will return to it.

Thanks,
John

 

[Jon Peltier]

John -

It depends on how badly you want to show what you want to show.

You can speed up the VBA if you put your code within these lines:

Application.ScreenUpdating = False
' code in here
Application.ScreenUpdating = True

and also if you format the points without selecting each one first.

This method is pretty quick, and doesn't require a slow VBA procedure:

You could have two actual series per apparent series. One is formatted
with lines and includes every point, The second is formatted with
markers and lines, and includes every Nth point, using Bernard's
formula. Draw your chart with two series, then delete the unneeded
legend entries: single click twice to select the legend entry (the text,
not the symbol), and press Delete.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[John]

I already have screenupdating = false, but thanks for the suggestion.

I rewrote the code to start with the Marker style set at the series level
and the Legends are okay IF there are 2 or more series. I still get the
legend with each point listed with only one series. For now I just don't use
markerss if there is only one series.

For some reason when I add new charts as a separate sheet with my company
laptop connected to the network at home vis VPN it takes a long time to
create the chart with user-specific information. The same behavior exists
when I add new charts when I am not connected via VPN to the company network
but am still connected to the internet. However, when I disable the internet
connection the VBA code is very fast. I haven't had time to figure out this
behavior.

When I run the code with the laptop in the docking station at work the speed
is better, but it is possible that the only difference is the speed of the
internet connection.

John