Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

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
    4,548

    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
    _______________________________________________
    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
    4,548

    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
    _______________________________________________
    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


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 HELP
    Replies: 11
    Last Post: August 22nd, 2007, 02:10
  2. accessing backcolor property of a shape
    By skibikegolf in forum EXCEL HELP
    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