OfficeTips Home || VBA Section || General Section || Download Section || Privacy Policy

Programmatically offsetting Microsoft Graph data labels
 

How often have you encountered this -  you need to nudge the data label just that little bit left or top or down and you do it manually and all hell breaks loose? It's a lot easier doing it programmatically. This little snippet just shows you how easy it really is to nudge the data label and get the position just right.

' --------------------------------------------------------------------------------
' Copyright ©1999-2018, Shyam Pillai, All Rights Reserved.
' --------------------------------------------------------------------------------
' You are free to use this code within your own applications, add-ins,
' documents etc but you are expressly forbidden from selling or
' otherwise distributing this source code without prior consent.
' This includes both posting free demo projects made from this
' code as well as reproducing the code in text or html format.
' --------------------------------------------------------------------------------

Sub OffsetDataPoints()
Dim oChart As Chart
Dim x As Integer, y As Integer
' Set the reference to the Chart object
Set oChart = ActivePresentation.Slides(1).Shapes(1).OLEFormat.Object
' Iterate thru each series
For x = 1 To oChart.SeriesCollection.Count
' And then thru each data point in the series
    For y = 1 To oChart.SeriesCollection(x).Points.Count
    With oChart.SeriesCollection(x)
' If the datapoint has a label...
        If .Points(y).HasDataLabel Then
' Offset the label 10 points (unit of measurement) above
           .Points(y).DataLabel.Top = _
                .Points(y).DataLabel.Top - 10
        End If
    End With
    Next y
Next x
' Update the graph and quit the graph application
oChart.Application.Update
oChart.Application.Quit
End Sub


If the selection type is text, then the ActiveWindow.Selection.TextRange.Start property returns the current position at which the selection begins within the parent text range. In this snippet we try to determine within which paragraph in the text range does the cursor selection begin.


' --------------------------------------------------------------------------------
' Copyright ©1999-2018, Shyam Pillai, All Rights Reserved.
' --------------------------------------------------------------------------------
' You are free to use this code within your own applications, add-ins,
' documents etc but you are expressly forbidden from selling or
' otherwise distributing this source code without prior consent.
' This includes both posting free demo projects made from this
' code as well as reproducing the code in text or html format.
' --------------------------------------------------------------------------------


Sub CurrentTextSelectionParaPosition()
Dim oShp As Shape
If ActiveWindow.Selection.Type = ppSelectionText Then
    Set oShp = ActiveWindow.Selection.ShapeRange.Item(1)
    With ActiveWindow.Selection.TextRange
        For I = 1 To oShp.TextFrame.TextRange.Paragraphs.Count
            If oShp.TextFrame.TextRange.Paragraphs(I).Start + _
                    oShp.TextFrame.TextRange _
.Paragraphs(I).Length > .Start Then
                MsgBox "Current selection begins in paragraph number: " & I
                Exit For
            End If
        Next I
    End With
End If
End Sub

This snippet looks with a table and returns the paragraph within which the current selection is located and the cell information

Sub CurrentTextParaPositionWithinTable()
Dim oTbl As Table
Dim oTR As TextRange2
Dim ColIndex As Long
Dim RowIndex As Long
Dim I As Long
If ActiveWindow.Selection.ShapeRange(1).HasTable Then
    Set oTbl = ActiveWindow.Selection.ShapeRange.Table
    For RowIndex = 1 To oTbl.Rows.Count
        For ColIndex = 1 To oTbl.Columns.Count
            If oTbl.Cell(RowIndex, ColIndex).Selected Then
            Set oTR = oTbl.Cell(RowIndex, ColIndex).Shape.TextFrame2.TextRange
            With ActiveWindow.Selection.TextRange
                For I = 1 To oTR.Paragraphs.Count
                    If oTR.Paragraphs(I).Start + oTR.Paragraphs(I).Length > .Start Then
                        MsgBox "Current selection begins in paragraph number: " & _
                                    I & " in row " & RowIndex & _
                                        " column " & ColIndex
                        Exit For
                    End If
                Next I
            End With
            End If
        Next
    Next
End If
End Sub

 

 


Copyright 1999-2018 (c) Shyam Pillai. All rights reserved.