Microsoft KB Archive/160527

= Microsoft Knowledge Base =

XL: Using the Same Marker for Shared Point on Multiple Series
Last reviewed: July 8, 1997

Article ID: Q160527

The information in this article applies to:


 * Microsoft Excel 97 for Windows
 * Microsoft Excel for Windows 95, version 7.0
 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SUMMARY
In a Microsoft Excel line or xy (scatter) chart, all data points in a series use the same data marker style. However, when data points from multiple series cross the same tick mark, you may want to use one data marker for all the data points. This article contains an a Visual Basic for Applications macro example that demonstrates how to do this.

MORE INFORMATION
Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft Product Support Services (PSS) Engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Example
The example in this article describes how to create a line or xy (scatter chart) and use a macro that formats the chart to use the same data marker for a data point on multiple series. The example involves the following steps:


 * Typing Sample Data
 * Creating the Chart and a Visual Basic Module
 * Creating the Macro

Typing Sample Data
Before you create a sample chart and use the macro in this article, type the following data on a worksheet:

B2: 1960    C2:  1961    D2:  1962    E2:  1963 B3: 37000   C3:  33000   D3:  29000   E3:  10000 B4: 23000   C4:  24000   D4:  25000   E4:  15000 B5: 25000   C5:  28000   D5:  31000   E5:  20000 B6: 10000   C6:  5000    D6:  3000    E6:  25000

Creating the Chart and a Visual Basic Module
Microsoft Excel 97:


 * 1) Select the range B2:E5. On the Insert menu, click Chart.
 * 2) In the "Chart Wizard - Step 1 of 4 - Chart Type" dialog box, click Line or XY (Scatter), and click Next.

NOTE: The macro example in this article works only with line or xy (scatter) charts.


 * 1) In Step 2 of the wizard, click Rows.
 * 2) Click the Series tab, and click the "Category (X) axis labels" box, and select the range $B$2:$E$2 on the worksheet (note that you may need to move the dialog box to see the worksheet). Then, click Finish.
 * 3) On the Tools menu, point to Macro, and click Visual Basic Editor. On the Insert menu, click Module.

Microsoft Excel 5.0 and 7.0:


 * 1) Select the range B2:E5. On the Insert menu, point to Chart and click On This Sheet, or click ChartWizard on the Standard toolbar.
 * 2) Using the mouse, click on the worksheet and drag to draw a chart. In step 1 of the wizard, click Next.
 * 3) In Step 2 of the wizard, click either Line or XY (Scatter).

NOTE: The macro example in this article works only with line or xy (scatter) charts.


 * 1) Click Next twice. In Step 4 of the wizard, under "Data Series in", click Rows. Type 1 in the "Use First Row(s) for Category (X) Axis Labels" box, and click Finish.
 * 2) On the Insert menu, pointing to Macro, and click Module.

Creating the Macro
In the new macro module you created in step 5 in the "Creating the Chart and a Visual Basic Module" section, type the following macro and run it:

Sub FormatChartpoints

'Turn off screen updating. Application.ScreenUpdating = False

'Activate the sheet that contains the chart. Worksheets(1).Activate

'Count the number of charts on the active sheet. The last 'chart that is created will be modified. NoCharts = ActiveSheet.ChartObjects.Count

'Activate the last chart that was created. ActiveSheet.ChartObjects(NoCharts).Activate

'Count the number of points on a the first series. 'This assumes that each of the series has the same number of points. NoPoints = ActiveChart.SeriesCollection(1).Points.Count

'Loop through each of points on each series. For z = 1 To NoPoints

'Loop through each series. For i = 1 To ActiveChart.SeriesCollection.Count

'Select the series and data point. ActiveChart.SeriesCollection(i).Points(z).Select

'Change the color of each point. With Selection .MarkerBackgroundColorIndex = z                  .MarkerForegroundColorIndex = z               End With

'Change the marker style, based on how many points are 'on each series. Note that there are only nine different 'data marker styles in Microsoft Excel. Select Case z                  Case 1 Selection.MarkerStyle = xlDiamond Case 2 Selection.MarkerStyle = xlSquare Case 3 Selection.MarkerStyle = xlTriangle Case 4 Selection.MarkerStyle = xlX Case 5 Selection.MarkerStyle = xlStar Case 6 Selection.MarkerStyle = xlDot Case 7 Selection.MarkerStyle = xlDash Case 8 Selection.MarkerStyle = xlCircle Case 9 Selection.MarkerStyle = xlPlus End Select

Next i      Next z

End Sub