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

Attached Images

	Code.PNG (46.3 KB, 17 views)
	Organiser.PNG (12.9 KB, 17 views)
	Toolbar.PNG (32.3 KB, 19 views)

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 which refers to the document or template that contains the code. To use the code with the current document use instead.

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?

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?

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?

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:
I get

Attached Images

Screenshot 2024-04-15 113945.png (9.5 KB, 17 views)

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?

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.

Attached Files

Investigation Template.dotm (40.9 KB, 4 views)

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.

Attached Files

Investigation Template v3.0.dotm (41.0 KB, 3 views)

You haven't changed the code:

Attached Images

Screenshot 2024-04-15 180335.png (3.6 KB, 13 views)

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.