excel vba - vba legend and plot area resizing -

excel vba - vba legend and plot area resizing -

i have excel chart changes on selections made in slicers. noticed plot area , legend area alter depending on made selection.

i tried prepare position , size plot area using vba, not work unfortunately.

the plot area , legend maintain on resizing, causing legend overlapping plot area. not want.

i have code, placed in worksheet page of vba editor:

option explicit private sub chart_calculate() chartobjects("grafiek 4").activate activechart.plotarea.width = 637.783 activechart.legend.left = 716.514 activechart.legend.width = 176.735 activechart.legend.height = 295.334 end sub

having code, assumed automatic resizing gone, saw legend still overlaps te plot area.

is there solution permanently fixes problem?

edit1:

yesterday, added few parameters plot area. seemed work then. tried again, , legend overlapping plot area again.

i changed code to:

option explicit private sub chart_calculate() chartobjects("grafiek 4").activate activechart.plotarea.top = 33.102 activechart.plotarea.left = 67.1 activechart.plotarea.width = 637.783 activechart.legend.top = 7 activechart.legend.left = 716.514 activechart.legend.width = 176.735 activechart.legend.height = 329.667 end sub

so 2 more paramters plot area.

edit2: have checked legend properties in excel. under 'options legend' there checkbox: show legend without overlapping plot area (i not know exact english language text). box checked, overlap plot area.

why impossible accomplish this? having fixed sizes plot area , legend should not hard.

edit 3: have routine in workbook:

option explicit private sub chart_calculate() chartobjects("grafiek 4").activate activechart .plotarea .top = 33.102 .left = 67.1 .width = 637.783 end .legend .includeinlayout = true .position = xllegendpositionright .autoscalefont = false .font.size = 8 .top = 5 .left = 706.899 .width = 179.735 .height = 336.681 end end end sub sub kopieergrafiek() activesheet.chartobjects("grafiek 4").copy end sub

(including suggestion in comment below post)

i not seem work. worksheet_change event perhaps works better?

edit 4: still not have solution issue. happens when name of 1 of legend items long fit space. , happens when there many items in legend fit in space available.

i think there no solution this. unless somehow tell excel maximize number of items in legend. or maximize length of series name.

i having problem myself legend resizing plot area. tried portland runner suggested, setting .legend.includeinlayout false (thus separating legend plot area suggested, perhaps made typo?) , plot area no longer resized.

excel-vba charts plot legend