Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: VBA Code To Set Shape Fill Effects Property To Gradient With Transparancy

  1. #1
    Join Date
    30th March 2009
    Posts
    2

    VBA Code To Set Shape Fill Effects Property To Gradient With Transparancy

    I'm looking for a way to set certain shape fill effects in VBA. For example, in Excel under Format Autoshape, Color, Fill Effects - I want to set the transparency "From" and "To" properties. The Fill properties of the shape correspond to the "From" transparency setting but I don't see any way to set the "To" transparency property using VBA. Also, the "Rotate Fill Effects with Shape" checkbox doesn't seem to have a corresponding VBA method.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    5,034

    Re: Setting Shape Properties Gradient Transparancy Rotate Fill Effects

    Hi Zaei,

    Welcome to the forum. Whenever I am stuck with a problem like this and I dont know the code right off the top of my head, I usually turn on the macro recorder and execute the steps that I need to write code for. The steps you describe should come up fine in the macro recorder. The resulting code can be found in a standard module in the VBE editor.

    If youre still having problems with the code the macro recorder produced, post back your results and let us know how we can help further.

    Ger

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  3. #3
    Join Date
    30th March 2009
    Posts
    2

    Re: Setting Shape Properties Gradient Transparancy Rotate Fill Effects

    Thanks for the reply I wish the answer was that simple. Let me explain using an example:

    Say I make a rectangle on a worksheet called Rectangle 1
    I can create a gradient using the following:

    VB:
    Sub Gradient() 
         
        Dim abc As Shape 
        Set abc = ActiveSheet.Shapes("Rectangle 1") 
         
        With abc 
             
            .Fill.Transparency = 0.25 
            .Fill.Visible = msoTrue 
            .Fill.ForeColor.SchemeColor = 24 
            .Fill.BackColor.SchemeColor = 34 
            .Fill.TwoColorGradient msoGradientHorizontal, 1 
             
        End With 
         
    End Sub 
    
    
    The gradient that is created uses two colors, but only the first color has transparency. The second color is opaque. I can manually set the transparency of the second color using

    FORMAT AUTOSHAPE : COLOR : FILL EFFECTS : TRANSPARANCY TO

    The macro recorder does not pick up any property associated with the transparency of the second color. I also cannot find any reference to the transparency setting for the second color by poking around in the shape properties in the VB editor locals window.

    Any help would be appreciated.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    5,034

    Re: VBA Code To Set Shape Fill Effects Property To Gradient With Transparancy

    Nope - I dont think its possible via VBA to set the transparency of the second gradient colour. Been trying in 2007 with no luck.

    Ger

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  5. #5
    Join Date
    4th April 2011
    Posts
    4

    Re: VBA Code To Set Shape Fill Effects Property To Gradient With Transparancy

    Hi,
    thisShape.Fill.GradientStops(2).Transparency = 1

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th February 2015
    Posts
    13

    Re: VBA Code To Set Shape Fill Effects Property To Gradient With Transparancy

    The hierarchy is:
    [CODE]
    worksheets
    ____+-----Shapes
    ___________+----- Fill
    _________________+----- GradientAngle
    __________________________+----- GradientStops
    ____________________________________+----- Item(x)
    ____________________________________+----- Color
    ____________________________________+----- Position
    ____________________________________+----- Transparency
    ____________________________________+----- Insert

    [Ignore the "___" as I had to use them to show the positions.]
    x = index value
    insert has the format: Insert(RBG, Position, Transparency, Index) "This allows you to insert color,position,transprancy and index all on one line"

    The debug.print in the code allows you to determine the number of gradient stops there are. If you get an error then there is no gradient.
    The code only shows one gradient. use the insert to add more at the index level you want.
    The index is a % level from 0 to 100
    For anyone in the future who needs to set the Gradient for a shape object use the following:
    VB:
    Debug.Print worksheets("worksheetname").Shapes("shapename").fill.GradientStops.count 
    With Worksheets("worksheetname").Shapes("shapename").Fill 
         
        .GradientStops.Item(1).Color.Brightness = 0 
        .GradientStops.Item(1).Color.RGB = RGB(0, 255, 0) 
        .GradientStops.Item(1).Color.TintAndShade = 0.1 
        .GradientStops.Item(1).Position = 0 
        .GradientStops.Item(1).Transparency = 0 
        .Fill.GradientAngle = 90 
         
    End With 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    26th February 2015
    Posts
    13

    Re: VBA Code To Set Shape Fill Effects Property To Gradient With Transparancy

    To add to a no gradient object use the following:
    This will make a 2 color gradient.

    VB:
    With Worksheets("worksheetname").Shapes("shapename").Fill        .Visible = msoTrue 
        .ForeColor.RGB = RGB(41, 156, 41) 
        .BackColor.RGB = RGB(77, 255, 77) 
        .TwoColorGradient msoGradientHorizontal, 1 
        .RotateWithObject = msoTrue 
    End With 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    5,034

    Re: VBA Code To Set Shape Fill Effects Property To Gradient With Transparancy

    Hi Steve - thanks for the input, I hope the Original Poster is not still waiting for a solution

    Thanks for following up though.

    Ger

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Fill Shape With Picture 2007
    By jolivanes in forum Excel General
    Replies: 11
    Last Post: August 22nd, 2007, 02:10
  2. accessing backcolor property of a shape
    By skibikegolf in forum Excel General
    Replies: 9
    Last Post: October 4th, 2005, 09:31

Bookmarks

Posting Permissions

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