Macro in template documents wont run in other documents

[Paulk66]

I wrote some code to colour lines in a table when I run the macro. The works fine in the template document itself (.docm), but when I open a document from the template it doesn't work.

I've a button to press, and also one on the ribbon (although I believe this is just the same thing in two different places). I've been doing VBA for Excel for years, but this is my first foray into Word. I feel I'm missing something, can anyone help?

Code is: (Also a snipping tool version in the attachments) - can't provide the actual document, sorry

Sub ColourTable()


Dim objTable As Table
Dim iRowCounter As Integer
Dim iColumnCounter As Integer

Dim lngBadColor As Long
Dim lngGoodColor As Long
' Predefine colors
lngBadColor = RGB(255, 0, 0)
lngGoodColor = RGB(169, 208, 142)

' Loop through all tables in document
For Each objTable In ThisDocument.Tables

For iRowCounter = 1 To objTable.Rows.Count

For iColumnCounter = 1 To objTable.Columns.Count

' Get Table cell
With objTable.Cell(iRowCounter, iColumnCounter)

If InStr(.Range.Text, "TO BE DONE") > 0 Then .Shading.BackgroundPatternColor = lngBadColor
If InStr(.Range.Text, "Yes") > 0 Then .Shading.BackgroundPatternColor = lngGoodColor
If InStr(.Range.Text, "N/A") > 0 Then .Shading.BackgroundPatternColor = lngGoodColor
If InStr(.Range.Text, "None") > 0 Then .Shading.BackgroundPatternColor = lngGoodColor

' If InStr(.Range.Text, "Very good") > 0 Then .Shading.BackgroundPatternColor = lngVeryGoodColor
' If InStr(.Range.Text, "Minor") > 0 Then .Shading.BackgroundPatternColor = lngGoodColor

End With

Next



Next

Next

End Sub

[Italophile]

Templates are .dotm, documents are .docm.

From your screenshot the code appears to be in your Normal.dotm, which is a template that only you have access to.

Your code uses

ThisDocument

which refers to the document or template that contains the code. To use the code with the current document use

ActiveDocument

instead.

[Paulk66]

Thanks for coming back to me, and apologies for the dotm/docm confusion.

I made the change you suggested, but unfortunately it's still not working. Do you have any other suggestions?

[Italophile]

From the screenshot in your question showing the Customize Quick Access Toolbar dialog you appear to have the same routine in three modules. It should only be in one, ideally the template (.dotm) that the document will be created from if the template is to be used by others.

Debugging code is very difficult when you can't see the context. For example:

How are you creating a new document from the template?
What are your macro security settings?
Is the template in a Trusted Location?

[Paulk66]

I appreciate debugging can be difficult like this, and I appreciate any help you can give me. For the points you made:

1. Closed everything down and opened Word again, and went back into the QA Toolbar. It only shows the module once now (on each side) and it's the 'Normal.module' one.
2. The template is stored in folder containing other templates (none of them have macros). It's accessed by clicking on 'File' and 'New'
3. Macro settings are 'Enabled'
4. I can't add the location to the Trusted Locations list. Looks like an Organisational setting that prevents it.

Will the lack of Trusted Location be potentially what's stopping it? If so, why does it work in the template, but not in new documents?

[Italophile]

If the code is in the Normal template then it will already be trusted.

What does "when I open a document from the template it doesn't work" mean exactly?

When I run your code:

Sub ColourTable()


    Dim objTable As Table
    Dim iRowCounter As Integer
    Dim iColumnCounter As Integer

    Dim lngBadColor As Long
    Dim lngGoodColor As Long
    ' Predefine colors
    lngBadColor = RGB(255, 0, 0)
    lngGoodColor = RGB(169, 208, 142)

    ' Loop through all tables in document
    For Each objTable In ActiveDocument.Tables

        For iRowCounter = 1 To objTable.Rows.count

            For iColumnCounter = 1 To objTable.Columns.count

                ' Get Table cell
                With objTable.Cell(iRowCounter, iColumnCounter)

                    If InStr(.Range.Text, "TO BE DONE") > 0 Then .Shading.BackgroundPatternColor = lngBadColor
                    If InStr(.Range.Text, "Yes") > 0 Then .Shading.BackgroundPatternColor = lngGoodColor
                    If InStr(.Range.Text, "N/A") > 0 Then .Shading.BackgroundPatternColor = lngGoodColor
                    If InStr(.Range.Text, "None") > 0 Then .Shading.BackgroundPatternColor = lngGoodColor

                    ' If InStr(.Range.Text, "Very good") > 0 Then .Shading.BackgroundPatternColor = lngVeryGoodColor
                    ' If InStr(.Range.Text, "Minor") > 0 Then .Shading.BackgroundPatternColor = lngGoodColor

                End With

            Next

        Next

    Next

End Sub

I get

[Paulk66]

Yeah, what you've shown me is how it's supposed to work (Like a visual checklist)

The template document is called 'Investigation Template'. When I open that actual document, the code works. (Opening the file from the actual file location)

When I open a new document based on 'Investigation Template' - which will open as Document 2 (for example), then it doesn't work. (Opening the file using 'File' and 'New' in Word).

Does that help explain?

[Italophile]

If the code that you are running is exactly as shown in my post then it should work.

Without a copy of your template I can't help any further.

I have attached a template that works correctly.

[Paulk66]

I've stripped out the confidential bits (which weren't part of the table).

If you're able to have a look it would be appreciated.

[Italophile]

You haven't changed the code:

[Paulk66]

I made the change, but looks like I changed in the wrong place.

Sorted that now and it works. Thanks very much for your help.