Solved

Display all labels outside of pie chart with leading lines

Posted on 2014-03-04
6
1,372 Views
Last Modified: 2014-03-17
I am generating a pie chart using vba from MSAccess.  With the best fit property setting, most of the labels are displayed outside of the pie chart.  However, we still have to manually drag out those labels that are short enough to fit within the pie slice.  

I would like to know how to iterate through each slice and determine which of the labels needs to be dragged away from the slice. Most importantly, I need to clearly display the leading lines connecting the labels to their respective slices.

Thanks.
0
Comment
Question by:yvroger
  • 3
  • 3
6 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39905797
Hi,

Instead of Best fit have tried Outside End

this code drag the labels by 2 points towards the outside so that you can see the lines

Sub macro()
ActiveChart.SeriesCollection(1).HasLeaderLines = True
ActiveChart.SeriesCollection(1).DataLabels.Position = xlLabelPositionOutsideEnd
Set chtArea = ActiveChart.ChartArea
MiddleOfAreaH = chtArea.Top + (chtArea.Height / 2)
MiddleOfAreaW = chtArea.Left + (chtArea.Width / 2)
For Each dtalbl In ActiveChart.SeriesCollection(1).DataLabels
If dtalbl.Top + (dtalbl.Height / 2) + chtArea.Top < MiddleOfAreaH Then
    dtalbl.Top = dtalbl.Top - 2
Else
    dtalbl.Top = dtalbl.Top + 2
End If

If dtalbl.Left + (dtalbl.Width / 2) + chtArea.Left < MiddleOfAreaW Then
    dtalbl.Left = dtalbl.Left - 2
Else
    dtalbl.Left = dtalbl.Left + 2
End If
Next
End Sub

Open in new window


Regards
0
 

Author Comment

by:yvroger
ID: 39908108
Rgonzo, your solution moved me in the right direction.  However, the height property in "dtalbl.Height" does not seem to work.  I get the error: "object doesn't support this property or method".

Please assist.  Thanks.
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39908487
Hi,

Could you send a dummy example?

Regards
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:yvroger
ID: 39917928
Before I come up with a dummy example let me ask you a question.

Weren't trying to use chtarea.Height and chtarea.Width instead of dtalbl.Height and chtarea.Width respectively?

For example the expression "If dtalbl.Top + (dtalbl.Height / 2) + chtArea.Top < MiddleOfAreaH" should have been "If dtalbl.Top + (chtarea.Height / 2) + chtArea.Top < MiddleOfAreaH", correct?
0
 

Author Comment

by:yvroger
ID: 39918049
Is there a way to determine the angle of the data label from the chart axis?
0
 
LVL 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 500 total points
ID: 39918067
Hi,

Chart area comprises the pie chart as well as the labels that's why you cannot have a precise angle for the labels. (Maybe you could but it would be really complicated)

I calculate the labels middle because if they a relatively large or high the direction to where I want to move them will be different

Regards
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now