Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro - Chart code (Excel 2007)

    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

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro - Chart code (Excel 2007)

    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

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro - Chart code (Excel 2007)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro - Chart code (Excel 2007)

    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

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro - Chart code (Excel 2007)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •