none
Excel 2007 shape callouts

    Question

  • Hello,

    I am having some problems with callouts in 2003 vs. 2007. 

    I am creating a callout using the code

    Set oCallout = ActiveChart.Shapes.AddCallout(msoCalloutThree, ActiveChart.PlotArea.Width, ActiveChart.PlotArea.Top, 200, 200).

    On the chart, one callout will have a line with an arrow pointing to one of several data series in an x-y scatter plot.

    In 2003 I can use the following line

    shape.Callout.Angle = msoCalloutAngle45

    to set the angle of the line coming from the callout. 

    In 2007, however, this call doesn't seem to make any difference and the lines from multiple callouts are all different angles.

    Can anyone tell me the correct way to set the angle on a callout?

    Also, does anyone know of a resource, book or anything that shows the way something was done in code in 2003 and way it needs to be written in 2007?

    Thanks,

    Robert

    Wednesday, May 26, 2010 9:32 PM

All replies

  • Hi,

    Are you sure that code to set the angle worked in 2003. Why does the code not use the oCallout object you created?

    For me this worked.

    ActiveChart.Shapes.AddCallout msoCalloutThree, ActiveChart.PlotArea.Width, ActiveChart.PlotArea.Top, 200, 200
    activechart.Shapes(1).Callout.CustomLength 0
    activechart.Shapes(1).Callout.Angle = msoCalloutAngle45
    
    You can use the offline help to get more information on the calloutformat object.

     

     


    Cheers   www.andypope.info
    Thursday, June 03, 2010 8:48 AM
  • Hi All,

    Here is a snippet from a VB.NET project I've worked up to get around not being able to specify the absolute position of the callout "pointer".

    Hope you can get some use from it.

    'Copyright Information:
    '  Owning Entity:   Fike Corporation
    '  Release Date:    March 18, 2011
    '  Usage Terms:     Free for Any Use, Please give attribution.
    '  Author:          Carl E. McMillin
    '

    Imports System.IO
    Imports System.Math
    Imports System.Collections
    Imports System.Drawing
    Imports Microsoft.Office.Core
    Imports Microsoft.Office.Interop

    Public Class clsChartGenerator
    .
    .
    .

        'Creates a Callout Shape.
        '
        ' Notes:
        '   All coordinates are expressed in "document" units.
        ' Parameters:
        '   <app>                       IN: The Application.
        '   <newChart>                  IN: The chart to put the callout on.
        '   <controlLimit>              IN: PROPRIETARY: Info on which axis to place callout.
        '   <axes>                      IN: PROPRIETARY: A hash of axis definitions.
        '   <p>                         IN: Where the callout points to.
        '   <descr>                     IN: Callout text.
        '   <textLeft, TextTop>         IN/OUT:  IN - Desired Left/Top position of callout box.
        '                                       OUT - Actual Left/Top position of callout box.
        '   <textWidth, TextHeight>     OUT: Actual width/height of callout box.
        '   <textCenterX, textCenterY>  OUT: Actual center of callout box.
        '
        Function CreateCallout(ByRef app As Excel.Application, ByRef newChart As Excel.Chart, _
                               ByRef controlLimit As clsControlLimit, ByRef axes As Hashtable, _
                               ByRef p As PointF, ByVal descr As String, _
                               ByRef textLeft As Double, ByRef textTop As Double, _
                               ByRef textWidth As Double, ByRef textHeight As Double, _
                               ByRef textCenterX As Double, ByRef textCenterY As Double) As Excel.Shape
            Dim newCallout As Excel.Shape, axis As String = controlLimit.axis, _
                axisDef As clsAxisDef = axes.Item(axis)

            newCallout = newChart.Shapes.AddShape(MsoAutoShapeType.msoShapeRoundedRectangularCallout, _
                                                  p.X, p.Y, 10, 10)

            With newCallout.TextFrame
                .Characters.Text = (descr & Chr(10) & _
                                    Format(controlLimit.value, axisDef.unitFormat) & Chr(10) & _
                                    axisDef.units)
                .AutoSize = True
            End With

            'The different constants are for adjusting placement of callout near where
            'it points to depending on which axis it's on - adjust to taste.
            If axis.Equals("X") Then
                textTop = (p.Y + newCallout.Height * 0.5#)
                textLeft = (p.X - newCallout.Width * 0.8#)
            ElseIf axis.Equals("Y") Then
                textTop = (p.Y - newCallout.Height * 1.2#)
                textLeft = (p.X - newCallout.Width)
            Else
                textTop = (p.Y - newCallout.Height * 1.2#)
                textLeft = (p.X + newCallout.Width * 0.1#)
            End If

            With newCallout
                'Put where we want - Excel may dither to keep it visible.
                .Top = textTop : .Left = textLeft

                With .Fill
                    .Visible = Microsoft.Office.Core.MsoTriState.msoTrue
                    .Solid() : .ForeColor.RGB = controlLimit.color : .Transparency = 0.3#
                End With

                'Read back geometry attrs since Excel may have adjusted them to
                'keep them on the visible area of chart. Also, we want the width and height
                'to adapt to the text characters and font to keep it pretty.
                textLeft = .Left : textTop = .Top
                textWidth = .Width : textHeight = .Height

                'Adjustments are with respect to the CENTER of the object.  This will
                'also be the attachment point for line callouts.
                textCenterX = (textLeft + (textWidth / 2))
                textCenterY = (textTop + (textHeight / 2))
            End With

            CreateCallout = newCallout
        End Function

        'Creates a "normal" callout - that is, using only standard geometry/attributes of callout.
        '
        ' Notes:
        '   All coordinates are expressed in "document" units.
        ' Parameters:
        '   <app>                       IN: The Application.
        '   <newChart>                  IN: The chart to put the callout on.
        '   <controlLimit>              IN: PROPRIETARY: Info on which axis to place callout.
        '   <axes>                      IN: PROPRIETARY: A hash of axis definitions.
        '   <p>                         IN: Where the callout points to.
        '   <descr>                     IN: Callout text.
        '
        Sub AddNormalCallout(ByRef app As Excel.Application, ByRef newChart As Excel.Chart, _
                             ByRef controlLimit As clsControlLimit, ByRef axes As Hashtable, _
                             ByRef p As PointF, ByVal descr As String)
            Dim newCallout As Excel.Shape, _
                textLeft As Double, textTop As Double, _
                textWidth As Double, textHeight As Double, _
                textCenterX As Double, textCenterY As Double

            'Get a prebuilt callout.
            newCallout = CreateCallout(app, newChart, controlLimit, axes, p, descr, _
                                       textLeft, textTop, textWidth, textHeight, _
                                       textCenterX, textCenterY)

            'Adjustments are made with respect to the width/height
            'and CENTER of callout text box.
            With newCallout.Adjustments
                .Item(1) = (p.X - textCenterX) / textWidth
                .Item(2) = (p.Y - textCenterY) / textHeight
            End With
        End Sub

        'Creates a callout that uses a line connector to point to a location.
        '
        ' Notes:
        '   All coordinates are expressed in "document" units.
        ' Parameters:
        '   <app>                       IN: The Application.
        '   <newChart>                  IN: The chart to put the callout on.
        '   <controlLimit>              IN: PROPRIETARY: Info on which axis to place callout.
        '   <axes>                      IN: PROPRIETARY: A hash of axis definitions.
        '   <p>                         IN: Where the callout points to.
        '   <descr>                     IN: Callout text.
        '
        Sub AddLineCallout(ByRef app As Excel.Application, ByRef newChart As Excel.Chart, _
                           ByRef controlLimit As clsControlLimit, ByRef axes As Hashtable, _
                           ByRef p As PointF, ByVal descr As String)
            Dim newCallout As Excel.Shape, _
                textLeft As Double, textTop As Double, _
                textWidth As Double, textHeight As Double, _
                textCenterX As Double, textCenterY As Double, _
                newConnector As Excel.Shape, connectorNames As New ArrayList

            'Get a prebuilt callout.
            newCallout = CreateCallout(app, newChart, controlLimit, axes, p, descr, _
                                       textLeft, textTop, textWidth, textHeight, _
                                       textCenterX, textCenterY)

            'Put callout "line" endpoint in middle of callout
            With newCallout.Adjustments
                .Item(1) = 0 : .Item(2) = 0
            End With

            'Create a line connector to point to the location we want.
            newConnector = newChart.Shapes.AddConnector(MsoConnectorType.msoConnectorCurve, _
                                                        textCenterX, textCenterY, p.X, p.Y)

            'Make sure it's behind the callout.
            With newConnector
                .ZOrder(MsoZOrderCmd.msoSendBackward)

                With .Line
                    .InsetPen = True : .Weight = 2
                    .BeginArrowheadStyle = MsoArrowheadStyle.msoArrowheadOval
                    .EndArrowheadStyle = MsoArrowheadStyle.msoArrowheadTriangle
                    .ForeColor.RGB = controlLimit.color : .Transparency = 0.3#
                End With
            End With

            'Build an array of callout/line connector names so we can group them.
            connectorNames.Add(newCallout.Name)
            connectorNames.Add(newConnector.Name)

            'This is a way to use the more versatile <ArrayList> for adding elements,
            'then converting to the list to a simple array.
            Dim connectorNamesAsStr As String() = _
                CType(connectorNames.ToArray(GetType(String)), String())

            'Group the shapes - it's more esthetically pleasing to have them tied together.
            newChart.Shapes.Range(connectorNamesAsStr).Group()
        End Sub
    .
    .
    .
    End Class

    Friday, March 18, 2011 6:09 PM