xlLabelPositionBestFit for Bubble Chart in Excel

When I try to set the datalabels position for a bubble chart to xlLabelPositionBestFit, it always fail with error, "Run-time error '91', Object variable or With block variable not set".

When i replace xlLabelPositionBestFit with xlLabelPositionBelow, xlLabelPositionAbove, xlLabelPositionCenter, it works.  

My bubble chart labels just got so jammed together that i need to find a good way to space them out automatically.

Can anyone advise why the above doesn't work, and if there's a simple way to resolve the bubble chart label spacing problem?

Code segment below:
   Set WorkingSheet = Worksheets(ChartSheetName)
   With WorkingChart.SeriesCollection(1)
        .DataLabels.Select
        Selection.Position = xlLabelPositionBelow
   End With


Many thanks in advance for your advice.

Thanks,
Janice
janicepAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Best fit is not an option, either in VBA or in the UI, for data labels on a bubble chart - it only works on Pie charts as far as I know.

I don't know of any way to automatically space them best - my only suggestion would be that if your chart is that cluttered, it may not be the best choice of chart for the situation.
0
 
regmigrantConnect With a Mentor Commented:
I think xlLabelPositionBestFit is only for Pie charts, the others you have to handle manually

Reg
0
 
janicepAuthor Commented:
You've both confirmed what I have suspected.  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.