Reformatting a chart - Strange issue


Board Regular
Jan 3, 2008
I have a macro that does some reformatting of a chart (on it's own chart sheet) that changes adds a footer, put the plot area in a specific area (for page to page consistency), axis formatting, legend format.

My issue is that the macro must be run twice and with a manual input (Input box) to correct.

I had two major issues to begin with. One was the plot area border which was fixed with help from another Excel website. That fix was totally unexpected and it involved moving the .Format.Line.Visible setting downwards lines.

  With ActiveChart.PlotArea
    .Width = 690
    .Height = 410
    .Top = 75
    .Left = 25
'  Order is important.
    .Format.Line.Weight = borderweight + 0.5
    .Format.Line.ForeColor.RGB = RGB(0, 0, 0)
    .Format.Line.Transparency = 0
    .Interior.ColorIndex = xlNone
    .Format.Line.Visible = msoTrue  '  Move from before changing the weight
The second which I still have not figured out. I printed out Chart area (CA), plot area (PA) and inside plot area (IPA) parameter at different times through the macro.

Test 1st-Start 1st-End 2nd-Start 2nd-End
CA Top 0 0 0 0
CA Left 0 0 0 0
CA Height 495.99 495.99 502.50 502.50
CA Width 683.49 683.49 747.75 747.75
PA Top 33.09 75 76.04 75
PA Left 7 18 20.07 25
PA Height 425.03 410 415.38 410
PA Width 661.49 661.49 723.68 690
IPA Top 33.09 81.65 82.68 81.65
IPA Left 47.79 78.71 80.77 85.71
IPA Height 425.03 381.43 386.82 381.43
IPA Width 608.16 578.29 640.48 606.79

(Sorry I tried to columnize the data).
CA Top0000
CA Left0000
CA Height495.99495.99502.50502.50
CA Top683.49683.49747.75747.75
PA Top33.097576.0475
PA Left71820.0725
PA Height425.03410415.38410
PA Width661.49661.49723.68690
IPA Top33.0981.6582.6881.65
IPA Left47.7978.7180.7785.71
IPA Height425.03381.43386.82381.43
IPA Width608.16578.29640.48606.79

Running the macro a third time does not change any values. I do nothing to the workbook between running the macro twice. Yet something happens the chart area after. I leave the macro.

I suspect part if the problem is that I can not set the Chart Area parameters.

Any Ideas?


Mike Virostko.

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics