Microsoft KB Archive/185306

= XL98: How to Change the Taper Style for All Series in a Chart =

Article ID: 185306

Article Last Modified on 6/17/2005

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q185306





SUMMARY
Microsoft Excel 98 includes many new built-in chart types, including cone and pyramid charts. By default, these chart types contain cones or pyramids that taper to a point for each data point in a series; the height for each cone or pyramid is equal to the value of the data point.

You can change the shape of the cones or pyramids for a series so that it tapers to the value of the largest data point in all the chart series. With this shape, all cones or pyramids for the series are the same, but they are truncated according to the values of the data points. Note that you cannot manually change the cones or pyramids for all of the series in a chart at one time. You must change the shape of the cones or pyramids for each series separately (one series at a time). To change the cones or pyramids for all the series at one time, you must use a Visual Basic for Applications macro.

This article contains a sample macro that modifies all the series in a chart to the same shape.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Creating the Sample Chart
Before you use the sample macro, create a chart to modify. To do this, use the following steps:

 Save and close all open workbooks, and then create a new workbook.  In Sheet1 of the new workbook, type the following data:      A1: a   B1: 1   C1: 3   D1: 2 A3: b  B2: 4   C2: 2   D2: 1 A3: c  B3: 5   C3: 1   D3: 3  Select cell A1, and then click Chart on the Insert menu. In step 1 of the Chart Wizard, click Cone in the Chart Type list. Under Chart Sub-type, click the sample chart in the third row of types and click Finish.</ol>

An embedded 3-D Cone chart appears on Sheet1.

NOTE: The cones for each data point taper to a point.

Sample Macro for Changing Cone Shapes
<ol> Start the Visual Basic Editor (press OPTION+F11).</li> On the Insert menu, click Module.</li>  Type the following code in the module: Sub Change_Chart_Shapes

Dim x As Chart Dim y As Series

' x is the Chart object. Set x = ActiveSheet.ChartObjects(1).Chart

' Loop through all the chart series. For Each y In x.SeriesCollection

' Set the series chart subtype. y.BarShape = xlConeToMax

Next End Sub </li> Run the Change_Chart_Shapes macro.</li> Switch to Microsoft Excel (press OPTION+F11).</li></ol>

Each of the cones (except one) in the chart no longer tapers to a point. The only cone that tapers to a point is the cone that represents the value in cell B3, which is the greatest value in all of the chart series.

<div class="references_section">