VBA formatting a shape

[Pavertheway]

Hi,

I have percentage values in certain cells on a sheet (E3 currently displays 84% for example). I would like a shape which takes the value of E3, and then applies that percentage to a gradient fill level of the shape, starting at the bottom.

So if the shape (an Oval), was to take 50%, then the bottom half of it would be blue, and the top half empty.

Is this possible to do in VBA? I know I can use conditional formatting to match 50% to a specific colour, but I don't think conditional formatting can be used to apply to a gradient fill (as far as I know, I am very willing to be proved wrong!)

Any help would be greatly appreciated.

Thank you!

 

[pgc01]

Hi

Use the Chord shape. It's like an oval just partially filled.
You can adjust the way it's filled.

Insert a Chord in your worksheet and play with it to see that you can get what you need.
Click on the end points of the line segment (small rectangles) and move them to adjust how the chord is filled.

 

[Pavertheway]

Hi,

Thanks for your reply and I have played around with the Chord but it doesn't do quite what I need.

I basically want a shape (Oval per se, but could be any shape really) that gives a filled amount based on a percentage in a cell - so you can see the filled percentage, almost like a tank filling up.

I can use conditional formatting data bars to display this in a single cell, however I would like it in a shape so it is more visual, and a vertical fill rather than horizontal.

Thanks for your time!

 

[pgc01]

Hi

I also played with the chord and i could do exactly what you want (half full, 20% full, etc.)
It's just a matter of moving the 2 small rectangles (the ends of the line segment) to the right place.
I could make it partially filled both horizontally and vertically.

You'll have then to implement the code in vba to adjust the shape according to the value in the cell, but first try moving the 2 end points of the line segment to see that you can achieve what you need.

 

[Pavertheway]

Hi,

I have been playing with it and I think I see what you mean - I have got it to be able to adjust fully and rotated it etc so that it it moves vertically. When the Chord is at 50% value, is there any way to keep the whole outline (so that it shows as a 50% full shape - currently I have just placed it over a second shape, with no fill and the outline the same colour as the Chord - so visually it works).

What would the VBA code be to adjust the position of the Chord to reflect a percentage in a cell? I have no idea how that would formulate.

Thank you for your time!

 

[pgc01]

is there any way to keep the whole outline (so that it shows as a 50% full shape - currently I have just placed it over a second shape, with no fill and the outline the same colour as the Chord - so visually it works).

That's the way I'd do it

 

[pgc01]

What would the VBA code be to adjust the position of the Chord to reflect a percentage in a cell? I have no idea how that would formulate.

Let's split this in 2.

The first part is to see what's the equation that gives you the value of the end points in your shape.
This is not an excel problem, this is mathematics.
You surely remember the ellipse equation. You can derive from it the area of a ellipse segment.
Unfortunately it's not an easy formula.

The second part is to implement it in excel. That is, of course, possible if you have the equation.

You can see how to calculate the area of an ellipse segment for ex. in this mathematics forum:

https://math.stackexchange.com/questions/458523/how-to-find-the-area-of-a-segment-of-an-ellipse



Remark:
Althouth this is possible to implement I think it's a bad idea.

To display how much a tank contains I'd use a rectangle.
With a rectangle the amount of filling will be proportional to the height or width of the rectangle. This will be very easy for the user to read.

With an oval the user cannot really see visually how much the tank contains. They would have to guess how much percentage of the total area is the ellipse segment displayed.
It will also be much more complex to determine the end points of the segments which you'll have to do in the implementation in vba.

The idea of a graphic display of quantities is to convey information is a quick, easy to read way. A rectangle would be my choice.

 

[Pavertheway]

Hi,

I do think you are right regarding the rectangle - it would be a clearer design and (hopefully) easier to create in VBA.
I suppose that brings me back to my original question - how do I fill a shape (Rectangle) based on a percentage value of another cell using VBA?

Thanks for all your help.

 

[Yongle]

how do I fill a shape (Rectangle) based on a percentage value of another cell using VBA?

To understand the method, test precisely as listed below in a new sheet so that there are no legacy values to confuse anything

- add a rectangle (shouldl be Rectangle 1)
- manually amend shapes fill properties to Gradient Fill with 3 Gradient Stops
- manually amend colour of Stop 1 to Background White
- manually amend colour of Stop 2 to Dark Blue
- manually amend colour of Stop 3 to Light Blue
- in cell A1 put in any value between 0 and 1
- run the macro below

Sub GradShape()
    Dim Prop As Double: Prop = 1 - Range("A1")
    ActiveSheet.Shapes("Rectangle 1").Select
    With Selection.ShapeRange.Fill
        .GradientStops(3).Position = 1
        .GradientStops(2).Position = Prop
        .GradientStops(1).Position = Prop
    End With
End Sub

 

[Pavertheway]

Hi,

This is amazing! Thank you.
In the final Workbook, I have seven shapes that I would like this to work from. So Rectangle 1 is based on A1, Rectangle 2 is based on B2, etc etc.
What aspect of the Macro do I need to duplicate to do this?

Thank you so much for your time.