Charts in Excel Created by VBA from Access 1

[tbg130]

Hi,

I would like to make some charts in Excel with some data that was created and exported from Access to Excel. When I generate the pie charts and set up all the data labels, they seem to overwrite each other when the percentages are small. How can set this up so that all the labels are visible and not overlapping each other? Also, how do I set the size of the pie chart within the chart window?

Basically, the way it is done now, the user can't make much sense of the data and has to adjust all the datalabels etc. which is a waste of time...

Thanks for your help...

 

[SkipVought]

tbg130,

I have repositioned data labels in other types of charts via VBA, but this is NOT a trivial task. It's possible, but the coding will take some thought -- have to work with radians and a variable radius.

But here's some code to play with that positions & sizes the chart and plot area...

Sub ConditionPieChart()
Sub ConditionPieChart()
   With ActiveSheet.ChartObjects(1)
   'position the chart on the sheet
      .Top = Application.InchesToPoints(2)
      .Left = Application.InchesToPoints(2)
      .Width = Application.InchesToPoints(5)
      .Height = Application.InchesToPoints(5)
      With .Chart
      'position the plot area within the chart
         With .PlotArea
            .Top = Application.InchesToPoints(1)
            .Left = Application.InchesToPoints(1)
            .Width = Application.InchesToPoints(3)
            .Height = Application.InchesToPoints(3)
         End With
      End With
   End With
End Sub

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[SkipVought]

Try playing aroud with this...

Sub PositionDatalabels()
   i = 1
   For Each dp In ActiveSheet.ChartObjects(1).Chart.ChartGroups(1).SeriesCollection(1).DataLabels
      With dp
         Select Case i
            Case 1
               .Position = xlLabelPositionOutsideEnd
            Case 2
               .Position = xlLabelPositionInsideEnd
            Case 3
               .Position = xlLabelPositionCenter
               i = 0
         End Select
         i = i + 1
      End With
   Next
End Sub

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}