Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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

    _______________________________________________

    Comment


    • #3
      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:

      Code:
      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.

      Comment


      • #4
        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

        _______________________________________________

        Comment


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

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

          Comment


          • #6
            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:
            Code:
            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

            Comment


            • #7
              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.

              Code:
              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

              Comment


              • #8


                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

                _______________________________________________

                Comment

                Working...
                X