Solved: Format Chart Area

[MonteCristo]

Hi,

I tried to format a chart through the "record macro" option in Excel 2007, but unfortunatelly not very much gets recorded... For example, if I select "chart area" and choose a fill option, nothing gets sent to VBA. The same with "plot area", I wanted a gradient fill with some transparency, nothing in VBA after macro. The only thing that does get sent to the code is the trendline, but with problems as well... If I keep clicking the button with the trendline macro, it keeps adding trendlines for every click.

Can you guys help with all these issues? I'm not looking to record macros, but to write VBA code for those options I want.

Thank you.

[jolivanes]

MonteCristo.
I usually copy my file onto a computer with 2003, use the macro recorder and clean things up after.
The only other way I can think of is using the object browser when you have the module sheet open. Find "Chart" in the "Classes" window and you'll see the "Members of Chart" to the right.
If all else fails, use the "Search" option in this forum or Google it.
Regards
John

[jolivanes]

Following were quickly made with the macro recorder and not cleaned.



Sub Macro1()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Fill.Patterned Pattern:=msoPattern60Percent
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 3
.Fill.BackColor.SchemeColor = 1
End With
End Sub




Sub Macro2()
ActiveChart.ChartArea.Select
With Selection.Border
.ColorIndex = 1
.Weight = 1
.LineStyle = 1
End With
Sheets("Plot Chart").DrawingObjects("Chart 2").RoundedCorners = True
Sheets("PPlot Chart").DrawingObjects("Chart 2").Shadow = False
Selection.Fill.Patterned Pattern:=msoPatternWideUpwardDiagonal
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 4
.Fill.BackColor.SchemeColor = 1
End With
End Sub


If you don't have access to 2003, let me know what you need and I'll run it through the macro recorder.

Regards
John

[MonteCristo]

Thanks John. I'll try to see what those macros do to my chart right away. I'll let you know presently if I need something else...


Thanks again.

[MonteCristo]

Hi again. It works ok, just the way it colours it it's a bit funny, but I guess I have to play with it a bit. Could you please highlight where the transparency for the plot area is? I can't seem to pin-point it.

Thanks so much.

[jolivanes]

It does not support transparency. For a workaround, see:

http://spreadsheetpage.com/index.php/tip/creating_a_transparent_chart_series/

As far as colors are concerned, these were just arbitrarily picked.

HTH

John

[MonteCristo]

Hi John,

If transparency is not supported, then why can I set the plot area gradient fill to be some % transparent? That's in Excel, in VBA it still eludes me.

It has to be a way. So far I only managed to set the chart area to be transparent... It drives me nuts because transparency is a property of fill or fillformat which are not part of plotarea... Actually they are, but PlotArea.Fill.Transparency gives an error. I need Shapes().Fill.Transparency. And I cannot seem to set the shape to point to plot area, if only I could do that, then it would be all over :banghead: . Frankly, VBA programming is not about playing around with colours, screw it, but I really need it by tomorrow night.

Thanks for the help. Any other ideas?

[MonteCristo]

Figured it out.

.PlotArea.Format.Fill.Transparency = 0.5

I cannot believe I spent so many hours trying to figure it out. The Object Browser is absolutely useless, I started from Transparency and moved upwards, never got anywhere... Many thanks to google in the end... phew!

[jolivanes]

Congratulations. I guess when you persevere, you'll get it in the end.
Thanks for letting us know. Now I also know how to do it.

Regards
John