I love the Leader Lines that Excel 2013 puts in for you if you’re using Excel 2013 and you drag a datalabel somewhere. If you want to use Leader Lines in earlier versions, then they are only available if you use a pie chart. And we all know what Peltier would say about that, don’t we.
So I thought I’d have a crack at programatically adding leader lines to ‘Peltier-approved’ chart types via VBA shapes.
First, let’s look at what those native leader lines in Excel 2013 look like, eh?
Ha! Fooled you…those are my programaticaly applied lines in Excel 2010.
I added these via using a Freeform shape, and the code lives inside a Class Module, so that if you move a data label around, the leader line gets redrawn. For instance, let’s drag that 4th data label North-East:
Just like in Excel 2013, my routine intelligently connects the leader line to the appropriate side of the data label, depending on it’s position relative to the point it connects to. For instance, let’s park that 5th data label directly over the point it refers to:
…and now let’s put it on the left:
And if the data label obscures the point, no leader line is produced:
I used a Class Module to do this. In fact, this is the first Class Module I have ever written:
Option Explicit
Public WithEvents clsChart As Chart
Private Sub clsChart_Mouseup(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim IDNum As Long
Dim a As Long
Dim b As Long
Dim shpLeaderLine As FreeformBuilder
Dim pt As Point
Dim lKink As Long
lKink = 5
Dim bLabel As Boolean
Dim shp As Shape
ActiveChart.GetChartElement x, y, IDNum, a, b
If IDNum = xlDataLabel Then
On Error Resume Next
ActiveChart.Shapes("LeaderLine_" & a & "_" & b).Delete
On Error GoTo 0
bLabel = True
Set pt = ActiveChart.SeriesCollection(a).Points(b)
Set shpLeaderLine = ActiveChart.Shapes.BuildFreeform(msoEditingAuto, pt.Left, pt.Top)
With shpLeaderLine
Select Case pt.Left
Case Is < pt.DataLabel.Left
.AddNodes msoSegmentLine, msoEditingAuto, pt.DataLabel.Left – lKink, pt.DataLabel.Top + pt.DataLabel.Height / 2
.AddNodes msoSegmentLine, msoEditingAuto, pt.DataLabel.Left, pt.DataLabel.Top + pt.DataLabel.Height / 2
Case Is > pt.DataLabel.Left + pt.DataLabel.Width
.AddNodes msoSegmentLine, msoEditingAuto, pt.DataLabel.Left + pt.DataLabel.Width + lKink, pt.DataLabel.Top + pt.DataLabel.Height / 2
.AddNodes msoSegmentLine, msoEditingAuto, pt.DataLabel.Left + pt.DataLabel.Width, pt.DataLabel.Top + pt.DataLabel.Height / 2
Case Else
Select Case pt.Top
Case Is < pt.DataLabel.Top
.AddNodes msoSegmentLine, msoEditingAuto, pt.DataLabel.Left + pt.DataLabel.Width / 2, pt.DataLabel.Top
Case Is > pt.DataLabel.Top + pt.DataLabel.Height
.AddNodes msoSegmentLine, msoEditingAuto, pt.DataLabel.Left + pt.DataLabel.Width / 2, pt.DataLabel.Top + pt.DataLabel.Height
Case Else
bLabel = False
End Select
End Select
If bLabel Then
Set shp = .ConvertToShape
With shp
.Name = "LeaderLine_" & a & "_" & b
.Line.ForeColor.ObjectThemeColor = msoThemeColorBackground1
End With
End If
End With
End If
End Sub
I use the Workbook_Open event to trigger this:
Option Explicit
Dim myChart As New clsChart
Private Sub Workbook_Open()
If Application.Version < 15 Then
Set myChart.clsChart = Sheet1.ChartObjects("Chart 1").Chart
End If
End Sub
Dim myChart As New clsChart
Private Sub Workbook_Open()
If Application.Version < 15 Then
Set myChart.clsChart = Sheet1.ChartObjects("Chart 1").Chart
End If
End Sub
I can’t say I fully understand Class Objects yet. For instance, I’d like to amend this code so that it works on multiple charts. I know the answer to this will be buried within Chip Pearson’s exhaustive but dense page at http://www.cpearson.com/excel/classes.aspx …but I’m too dense to absorb all this. I was kinda hoping you’d find it for me 
I also note that the code doesn’t always trigger for some reason, when moving the data label. I suspect it’s because I’m still moving the mouse at the time that I release the click button, as this only seems to happen when I’m moving the data label from left to right. Anyone got any pointers on this?
Here’s the file:
Twitter teams up with Thomson Reuters to give Eikon users tweets and analysis for listed firms
Feb03
While much of the attention and scrutiny around Twitter’s monetization efforts has revolved around advertizing, the company has been hard at work signing deals to sell its user data to companies with third-party services.
A partnership with 300 Entertainment, a music industry firm founded by ex-Warner Music Group head Lyor Cohen, was announced less than 24 hours ago, but it’s been joined by a new deal between Twitter and Thomson Reuters.
The latter will be offering what it describes as “sentiment analysis” of Twitter feeds in Thomson Reuters Eikon, a piece of market analytics and trading software. It will enable users to see the number of tweets linked to a listed company, whether they are positive or negative, and data for spotting notable trends or events as they occur in real-time.
Terms of the deal were not disclosed, but it represents another move by Twitter to sell its user data to companies and professionals that crave detailed analytics. Last week, Twitter also teamed up with CNN and Dataminr to give journalists alerts and notifications for important tweets as they break on Twitter.
➤ Press Release (Via TechCrunch)
Image Credit: Andrew Burton/Getty Images
Twitter teams up with Thomson Reuters to give Eikon users tweets and analysis for listed firms
Feb03
While much of the attention and scrutiny around Twitter’s monetization efforts has revolved around advertizing, the company has been hard at work signing deals to sell its user data to companies with third-party services.
A partnership with 300 Entertainment, a music industry firm founded by ex-Warner Music Group head Lyor Cohen, was announced less than 24 hours ago, but it’s been joined by a new deal between Twitter and Thomson Reuters.
The latter will be offering what it describes as “sentiment analysis” of Twitter feeds in Thomson Reuters Eikon, a piece of market analytics and trading software. It will enable users to see the number of tweets linked to a listed company, whether they are positive or negative, and data for spotting notable trends or events as they occur in real-time.
Terms of the deal were not disclosed, but it represents another move by Twitter to sell its user data to companies and professionals that crave detailed analytics. Last week, Twitter also teamed up with CNN and Dataminr to give journalists alerts and notifications for important tweets as they break on Twitter.
➤ Press Release (Via TechCrunch)
Image Credit: Andrew Burton/Getty Images
Americans buying Obamacare getting a good deal, study says
Feb03
on February 3, 2014
at 12:00 pm
Posted In: Affordable Care Act, Around the Web, Cincinnati, health care, health reform, mobiletopnews, Obamacare
About three million Americans have bought health insurance through a state or federal exchange set up under the Patient Protection and Affordable Care Act. And those consumers may be getting a pretty good deal, according to a report last week by a big accounting firm. In the report, PricewaterhouseCoopers (PwC) says that contrary to some initial concerns, plans […]