XL: How to Keep Unattached Text Aligned to a Data Series

ID: Q152403

The information in this article applies to:

Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Windows, versions 3.0, 4.0, 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a

SUMMARY

Microsoft Excel versions 3.0 and 4.0 have three macro functions that allow for the automatic positioning and sizing of chart items. These functions are GET.CHART.ITEM, FORMAT.MOVE, and FORMAT.SIZE.

These functions may assist in positioning a graphical object, such as an arrow, so that the object will be moved if the values in a data series are changed.

MORE INFORMATION

Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

In Microsoft Excel, a graphical object, such as an arrow, can be added to a chart as unattached text. If the values in a data series are changed, when the chart is updated, the unattached text will not move. This is by design.

The following are example macros which will automatically position an arrow so that it will point from the upper-right corner of the leftmost column, to the upper-left corner of the second column. This principal may be applied to other arrows or graphical objects. There are no Visual Basic for Applications equivalents for the GET.CHART.ITEM commands; therefore, you must use the Visual Basic for Applications method ExecuteExcel4Macro.

The method mentioned above will not work with a 3-D chart. For additional information, please see the following article in the Microsoft Knowledge Base:

Q93730 XL5: GET.CHART.ITEM() Returns Unexpected Results with 3-D Chart.

Microsoft Excel versions 5.0 and 7.0

Type the following code into a module sheet:


       Sub MoveArrows()
          ' Activate chart.
          ActiveSheet.DrawingObjects("Chart 1").Select
          ActiveSheet.ChartObjects("Chart 1").Activate
          ' Selects Series 1 Point 1.
          ActiveChart.SeriesCollection(1).Select
          ActiveChart.SeriesCollection(1).Points(1).Select
          ' Gets the upper-right x point of P1.
          xs1p1 = ExecuteExcel4Macro("get.chart.item(1,3,""s1p1"")")
          ' Gets the upper-right y point of P1.
          ys1p1 = ExecuteExcel4Macro("get.chart.item(2,3,""s1p1"")")
          ' Gets the upper-left x point of P2.
          xs1p2 = ExecuteExcel4Macro("get.chart.item(1,1,""s1p2"")")
          ' Gets the upper-left y point of P2.
          ys1p2 = ExecuteExcel4Macro("get.chart.item(2,1,""s1p2"")")
          ' Selects line 1, and then formats and moves the line.
          ActiveChart.DrawingObjects("Line 1").Select
          With Selection
             .Left = xs1p1
             .Top = xs1p2
             .Width = (xs1p2 - xs1p1)
             .Height = (ys1p2 - ys1p1)
          End With
          ' Selects Series 1 Point 2.
          ActiveChart.SeriesCollection(1).Select
          ActiveChart.SeriesCollection(1).Points(2).Select
          ' Gets the upper-right x point of P1.
          xs1p1 = ExecuteExcel4Macro("get.chart.item(1,3,""s1p2"")")
          ' Gets the upper-right y point of P1.
          ys1p1 = ExecuteExcel4Macro("get.chart.item(2,3,""s1p2"")")
          ' Gets the upper-left x point of P2.
          xs1p2 = ExecuteExcel4Macro("get.chart.item(1,1,""s1p3"")")
          ' Gets the upper-left y point of P2.
          ys1p2 = ExecuteExcel4Macro("get.chart.item(2,1,""s1p3"")")
          ' Selects line 2, and then formats and moves the line.
          ActiveChart.DrawingObjects("Line 2").Select
          With Selection
             .Left = xs1p1
             .Top = xs1p2
             .Width = (xs1p2 - xs1p1)
             .Height = (ys1p2 - ys1p1)
          End With
       End Sub

In a spreadsheet, type the following:


       A1: JAN     B1: 2.5
       A2: FEB     B2: 6.5
       A3: MAR     B3: 4.0

Select the Range A1:B3.

On the Insert menu, click Chart, and then click On This Sheet.

While holding down the left mouse button, drag the pointer to form the chart frame. When you have the frame size you want, release the left mouse button.

Click Next.

Click the Finish button.

Click the Drawing button on the toolbar, and on the Drawing toolbar, click the ARROW button. Place the arrow pointing from the top of the JAN column to the top of the FEB column.

On the Drawing toolbar, click the ARROW button. Place the arrow pointing from the top of the FEB column to the top of the MAR column.

Change the value in cell B1 to 5 and the value in cell B2 to 1. Note that the arrows do not move.

Run the MoveArrow macro to reposition the arrows.

Microsoft Excel versions 3.0 and 4.0

Type the following code on a Microsoft Excel 4.0 macro sheet:


      A1:   MoveArrow
      A2:   =SELECT("Chart 1")
      A3:   =ACTIVATE("Sheet1.xls Chart 1")
      A4:   xs1p1=GET.CHART.ITEM(1,3,"s1p1")
      A5:   ys1p1=GET.CHART.ITEM(2,3,"s1p1")
      A6:   xs1p2=GET.CHART.ITEM(1,1,"s1p2")
      A7:   ys1p2=GET.CHART.ITEM(2,1,"s1p2")
      A8:   =SELECT("arrow 1")
      A9:   =FORMAT.MOVE(xs1p1,ys1p1)
      A10:  =FORMAT.SIZE(xs1p2-xs1p1,ys1p2-ys1p1)
      A11:  xs1p1=GET.CHART.ITEM(1,3,"s1p2")
      A12:  ys1p1=GET.CHART.ITEM(2,3,"s1p2")
      A13:  xs1p2=GET.CHART.ITEM(1,1,"s1p3")
      A14:  ys1p2=GET.CHART.ITEM(2,1,"s1p3")
      A15:  =SELECT("arrow 2")
      A16:  =FORMAT.MOVE(xs1p1,ys1p1)
      A17:  =FORMAT.SIZE(xs1p2-xs1p1,ys1p2-ys1p1)
      A18:  =RETURN()

Explanation of macro code:

A1: Macro name
A2: Selects Chart
A3: Activates the chart
A4: Gets upper-right X of P1
A5: Gets upper-right y of P1
A6: Gets upper-Left x of p2
A7: Gets upper-left y of P2
A8: Selects Arrow 1
A9: Moves Arrow 1
A10: Resizes Arrow 1
A11: Gets upper-right X of P2
A12: Gets upper-right y of P2
A13: Gets upper-Left x of p3
A14: Gets upper-left y of P3
A15: Selects Arrow 2
A16: Moves Arrow 2
A17: Resizes Arrow 2
A18: Ends the macro

In a spreadsheet, type the following:


      A1:  JAN     B1:  2.5
      A2:  FEB     B2:  6.5
      A3:  MAR     B3:  4.0

Save the File as Sheet1.xls.

Select the Range A1:B3.

Click the Chart Wizard button.

While holding down the left mouse button, drag the pointer to form the chart frame. When you have the frame size you want, release the mouse button.

Click Next.

Click the Finish button.

Double-click the chart. On the Chart menu, click Add Arrow. Place the arrow pointing from the top of the JAN column to the top of the FEB column.

On the Chart menu, click Add Arrow. Place the second arrow pointing from the top of the FEB column to the top of the MAR column.

Change the value in cell B1 to 5 and the value in cell B2 to 1. Note that the arrows do not move.

Run the MoveArrow macro to reposition the arrows.

Additional query words: 3.00a 4.00a 5.00a 5.00c

Keywords : kbcode kbprg PgmHowto
Version : MACINTOSH:3.0,4.0,5.0,5.0a; WINDOWS:3.0,4.0,5.0,5.0c,7.0
Platform : MACINTOSH WINDOWS
Issue type : kbhowto