How do you change the color of a shape in a MS Word document using VBA triggered in MS Access?

Question

I'm using Access to open a word document and populate some fields in Word using data from Access. Here's that code (all working ok so far):

Private Sub cmdPopulateWord_Click()

    Dim appWord As Word.Application
    Dim doc As Word.Document
    Dim TestProspCode As String

    On Error Resume Next
    Err.Clear

    Set appWord = GetObject(, "Word.Application")

    If Err.Number <> 0 Then

        Set appWord = New Word.Application

    End If

    Set doc = appWord.Documents.Open("H:\Populate Word Document from Access.docx", , True)

    With doc

        .FormFields("wtxID").Result = Me!ID
        .FormFields("wtxFirstName").Result = Me!FirstName
        .FormFields("wtxLastName").Result = Me!LastName
        .FormFields("wtxDoB").Result = Me!DateOfBirth
        .FormFields("wtxProspCode").Result = Forms!tblWordDoc!tblProspCode_sub!ProspectusCode 
        .FormFields("wtxCourse").Result = Forms!tblWordDoc!tblProspCode_sub!Course

        .Visible = True
        .Activate

    End With

    Set doc = Nothing

    Set appWord = Nothing

    Exit Sub

errHandler:
MsgBox Err.Number & ": " & Err.Description

End Sub

I'm trying to see how I can also change the colour of shape already in the same Word document referenced in the above code.

Referring to some info here, I've tried inserting the code below straight after the with in the code above.

With doc

    .Shapes("Rounded Rectange 1").Fill.BackColor.RGB = RGB(0, 0, 0)
    .Visible = msoTrue

End With

There's no error, but the shape's colour does not change to black.

Try recording a macro in Word and then adding in references to a Word object. — Fionnuala
Didn't see you comment there! Thanks for this tip. I had a go at recording a macro (difficult in Word as the macro recorder limits what you can do with the mouse and didn't seem to want to record formatting). Anyway, found the way it needed to be referenced (see my answer below; seems to differ a bit with Microsoft's help pages). — Matt Hall

Kazimierz Jawor Kazimierz Jawor · Accepted Answer · 2014-01-17T05:31:43

What you are possibly looking for is .ForeColor property instead of .BackColor. See the code below where I additionally show how to change a border of the shape to make it look nice.

With doc.Shapes("Rounded Rectangle 1")
    'dark grey, (0,0,0) for black
    .Fill.ForeColor.RGB = RGB(80, 80, 80)
    'black borders
    .Line.ForeColor.RGB = RGB(0, 0, 0)
End With

How do you change the color of a shape in a MS Word document using VBA triggered in MS Access?

2 Answers