VBA - Change Shape inside a chart

Posted on 2012-09-09
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
    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

    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
    Can you upload a fake sample for playing?
    LVL 8

    Expert Comment

    by:Elton Pascua
        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


    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now