Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Graph properties in VBA
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Graph properties in VBA
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).MarkerSt yle = xlMarkerStyleDiamond - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Alan B" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining a 2-D column graph and a 2-D line graph on the same char | Charts and Charting in Excel | |||
How do I graph data daily as a line graph across a calendar format | Charts and Charting in Excel | |||
Creating a graph similar to a stacked column graph | Charts and Charting in Excel | |||
Hyperlinkage of one graph with another graph or Drill down graph | Charts and Charting in Excel | |||
Properties transferring from excel cells to word file properties | Excel Programming |