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

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


Who is Participating?
GrahamSkanConnect With a Mentor RetiredCommented:
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
Rex85Author Commented:
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.
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.

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Rex85Author Commented:
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?
ActiveDocument.Shapes.AddShape(msoShapeRectangle, 162#, 50#, 108#, 108#).Select
Selection.ShapeRange.Fill.Visible = msoTrue

This should give you more info:


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
Rex85Author Commented:

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.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.