Using VBA to create a 3-gradient fill in a newly created shape.

As we all know one of the more delightful improvements in Excel 2007 was removing the capability of recording shape formatting changes in VBA.  I'm looking for the simplest way possible to create a 3-gradient fill in a newly created shape.

Something that in effect says this, only with the proper terms and syntax

shp.select
shp.fill.gradientStop(1) = RGB(234,122,17)
shp.fill.gradientStop(1).stopPosition = 0%
shp.fill.gradientStop(2) = RGB(128,122,201)
shp.fill.gradientStop(2).stopPosition = 50%
shp.fill.gradientStop(3) = RGB(55,255,122)
shp.fill.gradientStop(3).stopPosition = 100%

shp.fill.gradientDirection = 90%

Given the fact that my terminology is probably all wrong, is it clear what I'm trying to accomplish?  :- )

Thanks,
John
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
0
 
Rory ArchibaldConnect With a Mentor Commented:
Perhaps something like
    Dim oFill As FillFormat
    Set oFill = ActiveSheet.Shapes(1).Fill
    With oFill
        .TwoColorGradient msoGradientHorizontal, 1
        With .GradientStops(1)
            .Color = RGB(234, 122, 17)
            .Position = 0
            .Transparency = 0
        End With
        With .GradientStops(2)
            .Color = RGB(55, 255, 122)
            .Position = 1
            .Transparency = 0
        End With
        .GradientStops.Insert RGB:=RGB(128, 122, 201), Position:=0.5, Transparency:=0
        .Visible = msoTrue
    End With

Open in new window

0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Great answers, thanks. Rgonzo1971, I like yours because the breadcrumbs are so easy to follow. And Rory, I like yours because it gives me a different way of doing it that might be more useful in certain situations. Plus, it's great to hear from you again!

Thanks,
John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.