Macro – Chart code (Excel 2007)

Topic

New Reply

Hi

I have a macro in Excel 2003, code below which creates a chart and puts a progress line, when I run the macro on Excel in Office 2007 it fails on the Transparancy command, anybody know why.

ActiveChart.Shapes.AddLine(intWeekWork1, 55, intWeekWork1, 360).Select
Selection.ShapeRange.Flip msoFlipVertical
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 1.5
Selection.ShapeRange.Line.DashStyle = msoLineDashDot
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 1
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Line.BeginArrowheadLength = msoArrowheadLengthMedium
Selection.ShapeRange.Line.BeginArrowheadWidth = msoArrowheadWidthMedium
Selection.ShapeRange.Line.BeginArrowheadStyle = msoArrowheadNone
Selection.ShapeRange.Line.EndArrowheadLength = msoArrowheadLengthMedium
Selection.ShapeRange.Line.EndArrowheadWidth = msoArrowheadWidthMedium
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadNone

Thanks

Mike

Reply | Quote

Replies

Mike

I’m not sure exactly why it fails, but you could try creating the chart in code without the progress bar, then record a new macro, create the progress bar manually, and view the code after you have done it. This should show what is happening.

Nick

Reply | Quote

That might not actually work, as the macro recorder in Excel 2007 tends to ignore anything you do with charts! I’m not sure whether it would count adding a shape to a chart as actual chart activity, though, and can’t test just at the moment.

Reply | Quote

Thanks Nick,

I removed the transparancy lines and the error went away, I will try the record when I get access to the machine with the latest version on it.

Regards

Mike

Reply | Quote

Mike

I looked up some old code & it is possible you need to set the Solid property before the Transparency :

With XLshp
With .Fill
.Solid
.ForeColor.SchemeColor = 9
.Transparency = 0.5
End With
.Line.Style = msoLineSingle
.Line.Visible = msoTrue
End With

HTH

Nick

Reply | Quote