Microsoft Knowledge Base

• 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.

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.

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).

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