Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Hampshire, United Kingdom
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Gradient fills in Excel 2007 (2007)

    I'm currently going through my VBA code and getting it to work in Office 2007. One problem I have come across that I can't easily resolve is doing a gradient fill. If you use the following code in 2003 it produces a rectangle with a red/green gradient fill. In 2007 it produces a red/white gradient fill, the .backcolor does not seem to behave as expected. Similar code in Word 2007 continues to behave as expected.

    With ActiveSheet.Shapes.AddShape(msoShapeRectangle, 90, 90, 90, 50).Fill
    .ForeColor.RGB = RGB(255, 0, 0)
    .BackColor.RGB = RGB(0, 255, 0)
    .TwoColorGradient msoGradientHorizontal, 1
    End With

    I notice there is a new user interface in Excel that includes multiple 'Gradient Stops', but unfortunately the usual trick of recording a macro whilst using this box provides absolutely no code at all. Can any one help me do a simple gradient fill?

    Regards

    Pete

  2. #2
    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: Gradient fills in Excel 2007 (2007)

    One of the problems with the new OfficeArt in 2007 is that the macro recorder ignores anything you do to it. This is a great shame given that charts are also now part of Office Art... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> I'm not in front of a machine with Office 2007 at the moment but should be tomorrow if no-one has come up with an answer by then. I have a vague recollection it has something to do with the <code>Fill.Format</code> or something similar.
    Regards,
    Rory

    Microsoft MVP - Excel

  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: Gradient fills in Excel 2007 (2007)

    It works OK for me if you change the order:
    <pre>With ActiveSheet.Shapes.AddShape(msoShapeRectangle, 90, 90, 90, 50).Fill
    .TwoColorGradient msoGradientHorizontal, 1
    .ForeColor.RGB = RGB(255, 0, 0)
    .BackColor.RGB = RGB(0, 255, 0)
    End With
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Jul 2002
    Location
    Hampshire, United Kingdom
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Gradient fills in Excel 2007 (2007)

    Thanks Rory,

    why is it the most simplest of things catch us out :-)

    Pete

  5. #5
    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: Gradient fills in Excel 2007 (2007)

    Because they're the things we pay less attention to... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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