Using Graph properties in VBA

[Alan B]

I have a sheet that runs a query on our ERP system. A second sheet is a
pivot table based on the returned data and a third sheet contains a graph
with buttons. Depending on which button is pressed, relevant data for that
department is returned. This is for yield analysis so I wanted all the
inputs to be one colour and all the outputs another. Every time the data
refreshes, the colours return to the default. I have a macro that reformats
the graph to the appropriate colours, but the issue now is that there were 4
ins and outs (ie 8 in total) when I wrote the macro, but this week there are
only 6 in total. The macro fails.

I am now trying to find away of drawing the graph with the default colours,
identifying how many points are on the graph then doing a FOR WHILE loop to
recolour the data points. ANy ideas how this could be done?

The line in my macro that fails is:

ActiveChart.SeriesCollection(1).Points(7).Select

as there are only 6 datapoits this week.

Regards

Alan Berry
find a way of identifying how many data points are on the graph then do it
using a a For While loop.

 

[Jon Peltier]

1. Count the points

Dim iPts as long
For iPts = 1 to ActiveChart.SeriesCollection(1).Points.Count
With ActiveChart.SeriesCollection(1).Points(iPts)
' blah blah
End With
Next

2. Don't select the points

ActiveChart.SeriesCollection(1).Points(7).Select
Selection.MarkerStyle = xlMarkerStyleDiamond

can be replaced by

ActiveChart.SeriesCollection(1).Points(7).MarkerStyle = xlMarkerStyleDiamond

- Jon