How do I Create, Position, and Conditionally Set the Color of, Autoshapes in Word with VBA

Posted on 2007-10-10
Last Modified: 2008-01-09
I have a Word VBA macro that creates a weekly chart by inserting images (of charts) that are created by Minitab and Excel into Word tables. The order and location of each chart is the same every week.

My problem is that I have been asked to add a small Red, Yellow, Green, indicator to reflect the status of the chart. This is based on the slope of the trend line and needs to refer to a cell in Excel where the slope is calculated and stored.

I have been doing this manually by creating rectangular Autoshapes and individually formatting them with the correct color. This is inefficient and error prone.

I would like my Word macro, after inserting the images, to refer back to the cell in Excel where the slope is stored. If the slope is > 0, I would like to create a green rectangualr autoshape of a given size and put it on top of the appropriate chart, i.e, the one referred to by the Excel cell with the slope value. If the slope is less than zero, I would like it to be red, and if the zlope just happens to be zero, I would like it to be yellow. (I did not choose those criteria)

It sounds complicated. I have "slightly beyond beginner" level experience in VBA macros in Excel, but have done nothing with shapes in Word.

Nothing in the existing set of answers seems to address my problem exactly.

Can anyone provide some guidance? I would appreciate it. Thanks


Question by:Rex85

    Author Comment

    Correction: My macro creates a weekly report, not a weekly chart. The charts, saved as images, are inserted into the report, which is a series of tables.
    LVL 76

    Expert Comment

    Sounds like quite a lot of work.

    There seem to be two areas to be addressed. You need to get the data that determines the colouring of the autoshapes. Also you need to be able to place, colour and size the autoshapes.

    Perhaps you should post your current macro, so we can better understand the scenario.


    Author Comment

    Here's a sample of the current Word macro that inserts updated charts. I haven't written the part that inserts and formats the autoshapes. That's the part I need help with.

        ActiveDocument.Tables(2).Cell(Row:=1, Column:=1).Select
        Selection.InlineShapes.AddPicture FileName:= _
            "P:\flexcel Manf BU Quality Data\OFG\Rex\New OFG PPT Charts\15th Panel FPY Trend.JPG" _
            , LinkToFile:=False, SaveWithDocument:=True

    The macro is a series of these statements, each Selecting a specific cell in a specific table in the Word Document, deleting  the current contents, and then insering the appropriate image. In this case, it is a JPG image of a First Pass Yield (FPY) Trend chart for a plant known as 15th St. These images are updated by an Excel macro that updates the chart, and then saves it as an image to allow it to be inserted using the method above.

    The autoshapes are maually formatted and manually inserted into the Word document by me. They are "on top" of the graph images.

    My thought is to add a section after each of the above set of statements that creates a rectangualr autoshape and places it on top of the image it just inserted

    Then, have the macro look at the value in a particular cell in Excel, unique to that chart, that contains a function with the slope of the trend line. ( I can't really do it in Word since the charts are images, there is no graph or data in word.) and then use that in a conditional If, Then, Else clause to set the format of the Autoshape to the appropriate color.

    I think I can figure out how to look at the value in Excel, but I have no idea how to create an Autshape of a particualr size and shape in Word and locate it in a specifc location on the page.

    Once I did that, I would then use the value returned from Excel to set the color using the If, Then, Else

    Does that make sense?
    LVL 65

    Expert Comment

    ActiveDocument.Shapes.AddShape(msoShapeRectangle, 162#, 50#, 108#, 108#).Select
    Selection.ShapeRange.Fill.Visible = msoTrue

    This should give you more info:


    LVL 76

    Expert Comment

    Sub ExcelData()
        Dim ilsh As InlineShape
        Dim sh As Shape
        Dim strPictureFile As String
        Dim strXlWorkbook As String
        Dim xlApp As Excel.Application
        Dim xlWbk As Excel.Workbook
        Dim xlWks As Excel.Worksheet
        Dim c As Integer
        Dim tbl As Table
        Dim r As Integer
        Dim t As Integer
        Dim docA As Word.Document
        Dim Colour As Long
        'set variables directly here. In full program, you probably have a routine to derive these in sequence
        t = 2: r = 1: c = 1
        'strPictureFile = "P:\flexcel Manf BU Quality Data\OFG\Rex\New OFG PPT Charts\15th Panel FPY Trend.JPG"
        strPictureFile = "F:\My Pictures\Ben\Guildford\frame_001.JPG"
        strXlWorkbook = "C:\Documents and Settings\Graham Skan\My Documents\book3.xls"
        Set docA = ActiveDocument
        Set tbl = docA.Tables(t)
        tbl.Cell(r, c).Range.Text = ""
        Set ilsh = docA.InlineShapes.AddPicture(strPictureFile, False, True, tbl.Cell(r, c).Range)
        Set sh = docA.Shapes.AddShape(msoShapeRectangle, 0, 0, 20, 10, ilsh.Range)
        Set xlApp = New Excel.Application
        Set xlWbk = xlApp.Workbooks.Open(strXlWorkbook)
        Set xlWks = xlWbk.Worksheets(1)
        Select Case xlWks.Cells(1, 1) ' use top lh cell of worksheet 1 for this example
            Case Is < 0
                Colour = vbYellow
            Case 0
                Colour = vbGreen
            Case Is > 0
                Colour = vbRed
        End Select
        With sh.Fill
            .ForeColor.RGB = Colour
            .Visible = msoTrue
        End With
    End Sub

    Author Comment


    When running this, I received a "Compile Error: User-Defined Type Not Defined" mesage regarding the Dim xlApp As Excel.Application line.

    Also, Could you expand on what you meant by "In full program, you probably have a routine to derive these in sequence" I'm not sure how I would do that.


    LVL 76

    Accepted Solution

    Sorry. You need to set a reference (Tools/References in the VB window) to the Microsoft Excel Object library.

    I have plucked the table number, cell location and file names out of the air.

    You have several to do so you would probably need to implement a loop of some sort.

    'table cell is always the same (Top LH). Table & files vary
    r = 1:c = 1
    For x = 0 to 2
        strPictureFile = "F:\My Pictures\Ben\Guildford\frame_00" & x & ".JPG"
        strXlWorkbook = "C:\Documents and Settings\Graham Skan\My Documents\book" & 3+ x & ".xls"
        t = x + 1
        Set tbl = docA.Tables(t)

        End With
    Next x

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    SSIS 2010 Convert to SSIS 2013 On Different Box 3 27
    stop word moving down 2 24
    Using "ScreenUpdating" 6 36
    VBA Help TT V-1.1 15 22
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

    731 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

    15 Experts available now in Live!

    Get 1:1 Help Now