Dynamically sizing the legend

[Brian]

Hi I'm automating excel from VB and generating charts. I need to be able to
dynamically size the chart so that all legend entries are visible. I'm
trying to get the 2 routines below working to acccomplish this. These
currently work in the VBA editor if I step through it, but if I just let it
run, it does not work. These will be moved to a VB dll when I have them
working and the workbook will not be visible when they run. Can this be
done?

TIA,

Brian

Public Function ChartLegendIsTooShort(oChart As Excel.Chart)
Dim blnShort As Boolean
Dim eEntry As Excel.LegendEntry

blnShort = False

If oChart.HasLegend Then
For Each eEntry In oChart.Legend.LegendEntries
If eEntry.Top + eEntry.Height > oChart.Legend.Height Then
blnShort = True
Exit For
End If
Next
End If

ChartLegendIsTooShort = blnShort

End Function

Public Sub AutosizeChart(oChart As Excel.Chart)
Dim blnTooSmall As Boolean

blnTooSmall = ChartLegendIsTooShort(oChart)
While blnTooSmall

'resize chart
ActiveSheet.Shapes(oChart.Parent.Name).ScaleHeight 1.05, msoFalse,
msoScaleFromTopLeft

'fit legend to chart
oChart.Parent.Activate 'without this it doesn't work in debug mode
oChart.Legend.Top = 0
oChart.Legend.Height = ActiveSheet.Shapes(oChart.Parent.Name).Height

blnTooSmall = ChartLegendIsTooShort(oChart)

Wend
End Sub

 

[Tushar Mehta]

Not that responding to this post guarantees a satisfactory resolution
to your problem...

What part doesn't work? How does it not work?

In general, I find adjusting a chart's element's location/size is a
high-risk proposition. It may work, it may not work, it kinda, sorta
could work...

Also, you might want to set the legend font to *not* autosize.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Brian]

The sizing of the chart was successful, but the sizing/positioning of the
legend was not. This caused an infinite loop in my code. I was able to
solve this problem by adding the line 'ochart.Legend.Select' to my
autosizechart routine.
The code now seems to work (even when the VB application is not visible).

Incidentally, I would have been fine setting the legend positioning to
'right' to allow excel to handle the sizing/positioning of the legend, but
it does not reduce the spacing between legend entries to maximize the number
of entries that fit without changing the font.

Thanks for the response,

Brian



Tushar Mehta wrote in message ...