VBA - Change Shape inside a chart

Posted on 2012-09-09
Medium Priority
Last Modified: 2012-09-10
Hi Guys,

We have three charts which are created by VBA by applying a chart template to get the format of the graph correct. The chart is then linked to the updated data source to form the chart. Each chart has a shape which needs to be changed depending on a certain criteria. The shapes in the attached image are all hexagon indicating a Major issue however this may need to be changed to a rectangle or triange with different text and colour. I need help being able to get a handle on the shape attached to each chart so I can change it's shape, colour and text if needed. Below is the code I have to apply the chart template and I have attached an image of what the charts look like. Any assistance would be very appreciated

ActiveChart.SetSourceData Source:=Range("'Sheet1'"!$A$20:$D$30")
' eg. If percentage < 10 Then change shape to a green triangle

graphs screenshot

Question by:victoriaharry
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38381590
One way could be to draw all shapes in the same place and bring the required one to the front so that the others are not visible.

Author Comment

ID: 38381677
Thanks for the reply. I should of mentioned that the hexagon shape is part of the chart so it appears when I later export the chart to an image file. If I draw the shapes over the top then when I export the chart they won't be included.
I think I need to find a way to get a handle on the shape inside the chart so I can manipulate it. I like the idea of adding all the shapes and making the required one visible but not sure how to embed them all in the template chart so they can be used
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38381701
Can you upload a fake sample for playing?

Expert Comment

by:Elton Pascua
ID: 38381778
    ActiveSheet.Shapes("Hexagon 3").AutoShapeType = msoShapeIsoscelesTriangle
    ActiveSheet.Shapes("Hexagon 3").Fill.ForeColor.RGB = vbGreen

Open in new window

Here are the other shape constants:

Open in new window

LVL 18

Accepted Solution

krishnakrkc earned 2000 total points
ID: 38381839

You could try some thing like this

Sub ChartShapes(ByRef ChtObject As Chart, ByVal Perc As Single)
    Dim shpShape    As Shape
    Dim i           As Long
    Select Case Perc
        Case Is <= 0.1
            With ChtObject
                On Error Resume Next
                Set shpShape = .Shapes("Triangle")
                On Error GoTo 0
                If shpShape Is Nothing Then
                    Set shpShape = .Shapes.AddShape(msoShapeIsoscelesTriangle, .PlotArea.Left + 5, .Parent.Top + 5, .PlotArea.Width - 10, .PlotArea.Top - 10)
                    shpShape.Name = "Triangle"
                    shpShape.Fill.ForeColor.RGB = RGB(0, 255, 0)
                    For i = 1 To .Shapes.Count
                        .Shapes(i).Visible = msoFalse
                    shpShape.Visible = msoTrue
                End If
            End With
        Case 0.1 To 0.5
            'copy the above code and adjust the shape name
        Case Else
    End Select
End Sub

Open in new window

and call the routine as

ChartShapes ActiveChart, 0.1

Open in new window


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question