ShapeRange.Fill Problem in Excel 2007

[idbr]

Hi,

Does anyone know how I can set a two colour gradient fill in E2007 VBA?

I output a report that has 2 rectangles as the background. Left rectangle is gradient filled colour 1 to colour 2. Right rectangle is gradient filled colour 2 to colour 3. The shapes are place dnext to each other to give the impression of a single bar with a three colour gradient 1 to 2 to 3.

This code works fine in 2003, but on running it in 2007, the second colour is not recognised and I get colour 1 to white to colour 2 to white.

Code:

    ' add the left hand blended shape
    ' argument order: type, left, top, width, height
    ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
                                GAP_LEFT_START_POSITION + 20, _
                                GAP_TOP_START_POSITION + ((i - 1) * GAP_BACK_BLOCK_HEIGHT), _
                                20 + GAP_BACK_BLOCK_WIDTH, _
                                GAP_BACK_BLOCK_HEIGHT _
                                ).Select
                                
    ' rename
    Selection.Name = "back_" & i & "_" & 2
                                
   ' set the display properties
   With Selection.ShapeRange.Fill
   
        .Visible = msoTrue
        .Solid
        .ForeColor.RGB = lngBlendStartColour
        .BackColor.RGB = lngBlendMidColour
        .TwoColorGradient msoGradientVertical, 1
        .Transparency = dblTransparency
        
    End With
    
    With Selection.ShapeRange.Line
    
        .Weight = 0.75
        .DashStyle = msoLineSolid
        .Style = msoLineSingle
        .Transparency = dblTransparency
        .Visible = msoFalse
        
    End With
    
    ' same again for the right hand shape
    ' argument order: type, left, top, width, height
    ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
                                GAP_LEFT_START_POSITION + GAP_BACK_BLOCK_WIDTH + 20, _
                                GAP_TOP_START_POSITION + ((i - 1) * GAP_BACK_BLOCK_HEIGHT), _
                                GAP_BACK_BLOCK_WIDTH, _
                                GAP_BACK_BLOCK_HEIGHT _
                                ).Select
                                
    ' rename
    Selection.Name = "back_" & i & "_" & 3
                                
   ' set the display properties
   With Selection.ShapeRange.Fill
   
        .Visible = msoTrue
        .Solid
        .ForeColor.RGB = lngBlendMidColour
        .BackColor.RGB = lngBlendEndColour
        .TwoColorGradient msoGradientVertical, 1
        .Transparency = dblTransparency
        
    End With

Any advice would be greatly appreciated.

Thanks, Iain

 

[SkipVought]

Hi,

Did you try turning on your macro recorder, changing the gradient colors and then observing your recorded code?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[idbr]

Hi Skip,

Macro recorder isn't working - or at least isn't picking up shpe operations. Useful.

Anyhow, it seems it's an operation order fernicketiness problem. Changing

        .ForeColor.RGB = lngBlendMidColour
        .BackColor.RGB = lngBlendEndColour
        .TwoColorGradient msoGradientVertical, 1

to

        .TwoColorGradient msoGradientVertical, 1
        .ForeColor.RGB = lngBlendMidColour
        .BackColor.RGB = lngBlendEndColour

Works just fine.

Progress, I love it. %-)