Note: the code needs an auto resizing textbox called "txtLegend1" on a sheet with a codename of wksLegend. In case anyone else is trying to achieve the same thing, the code below may give you a head-start.
I then add this growth factor to the calculated font size, apply this new font size to the legend and then increase the legend height fractionally.Įxcel then works some unknowable magic in the background and does a very good job of adjusting the spacing of the legend elements.Īlthough it's a little frustrating that I cannot directly control this spacing, the solution works well on all systems it's been tried on. Having calculated the optimum font size to fit all series elements in the legend I then calculate a 'growth factor' based on the difference between the calculate font sizeĪnd a predetermined 'threshold' font size. As expected, this took a bit of trial and error to come up with the optimum numbers.
0 kommentar(er)
