Chart Item coordinates and inserting text boxes

[korhan]

Hello everyone,

I am trying to insert text boxes onto my chart based on the bar chart coordinates. I am not sure if this is doable. I am trying to loop through the series and select and based on that selection I need to find upper left and right corner coordinates of that bar chart so that I can place the text box between those coordinates vertically not horizontally of course. Also, I cannot think of a method on how to scale this text box based on those coordinates. Is this possible with VBA? I tried to draw this below. Imagine that it is a chart and I am trying to find the coordinates of those 2 red lines and I need to loop through all the bar charts to get these coordinates.

Thanks for reading.

|
|    _
|   | [COLOR=#ff0000]|       [/COLOR]_
|   | |      | |
|   | |      | |
|_  | [COLOR=#ff0000]| [/COLOR]_ _  | | _ _

 

[korhan]

If anybody is curious this is what I have done and it is working perfectly(let's cross our fingers)

' Programmer: Korhan Tezel
' Date: 8/3/2016
' CustomLabelsForColumns macro will go through each series and its columns,
' find the coordinates of those columns and based on that information it
' will insert vertical texboxes in each of those columns.


Option Explicit


Sub CustomLabelsForColumns()
    
    ' Declare variables
    Dim wsKPI As Worksheet                  ' To hold worksheet object
    Dim ser As Series                       ' To hold series object
    Dim intActiveSeries As Integer          ' To hold active series number
    Dim dxVal3, dyVal3, dyVal5 As Double    ' To hold hold coordinate information of
                                            ' active column(series)
    Dim chtChart As Chart                   ' To hold the chart object
    Dim chtHeight, chtWidth, _
        chtLeft, chtTop As Double           ' To hold chart size data
    Dim strPerformance As String            ' To hold the name for performance label
    
    ' Initialize variables
    Set wsKPI = Worksheets("102")
    Set chtChart = wsKPI.ChartObjects("ChartUNS").Chart
    
    
    ' Loop through series, select corresponding column, get the coordinate information
    With wsKPI.ChartObjects("ChartUNS").Chart
        For Each ser In .SeriesCollection
            ser.Select
            ' If the current selected series is other than "5"
            If ExecuteExcel4Macro("Selection()") <> "S5" Then
                intActiveSeries = CInt(Right(ExecuteExcel4Macro("Selection()"), 1))
                ' Get x and y coordinates of the selected item on the graph
                dxVal3 = ExecuteExcel4Macro("GET.CHART.ITEM(1,3,""S" & intActiveSeries & "P1"")")
                dyVal3 = ExecuteExcel4Macro("GET.CHART.ITEM(2,3,""S" & intActiveSeries & "P1"")")
                dyVal5 = ExecuteExcel4Macro("GET.CHART.ITEM(2,5,""S" & intActiveSeries & "P1"")")
                
                ' Calculate height and width for the text box, insert and arrange the location
                ' of the text box
                With chtChart
                    chtHeight = .ChartArea.Height
                    chtWidth = .ChartArea.Width
                    chtLeft = .ChartArea.Left
                    .Shapes.AddTextbox(msoTextOrientationUpward, chtLeft - chtWidth + dxVal3, _
                                chtHeight - dxVal3, 20, dyVal3 - dyVal5).Select
                    With Selection.ShapeRange(1)
                        With .TextFrame2
                            ' Select Case for performance category label for columns
                            Select Case ser.Name
                                Case Is = "UNS Exceptional"
                                    strPerformance = "Exceptional"
                                Case Is = "UNS Good"
                                    strPerformance = "Good"
                                Case Is = "UNS Fair"
                                    strPerformance = "Fair"
                                Case Is = "UNS Poor"
                                    strPerformance = "Poor"
                                Case Else
                                    strPerformance = "Unknown"
                            End Select
                            ' Apply result to the text box
                            .TextRange.Characters.Text = strPerformance
                            .VerticalAnchor = msoAnchorMiddle
                            .TextRange.Font.Size = 18
                        End With
                        ' Set the position of the text box
                        .IncrementLeft -(chtWidth - dxVal3)
                        .IncrementTop chtHeight
                        .IncrementTop -dyVal5
                     End With
                End With
            End If
        Next ser
    End With
        
End Sub