Need quick and dirty VBA/Macro help in Word

March 10, 2016 8:35 AM

Conditional Formatting a drop-down list in a Word Document

I'm developing a Word Document to use for a 'mail merge' (it's not mail, it's the only way I can think of to create multiple documents from a list.)

I'm using tables and in one cell, I've added a drop-down to select a Red-Yellow-Green value. I'd like the whole cell to change to the appropriate color when the selection is made. I searched the web and found this code which should do this:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
If ContentControl.Title = "RAG" Then
With ContentControl.Range
Select Case .Text
Case "Red"
.Cells(1).Shading.BackgroundPatternColor = wdColorRed
Case "Yellow"
.Cells(1).Shading.BackgroundPatternColor = wdColorYellow
Case "Green"
.Cells(1).Shading.BackgroundPatternColor = wdColorGreen
End Select
End With
End If
End Sub

Now, anyone who knows me knows that I don't code. But...if this is the only way to do this, I'm on board.

I saved the document as Macro-Enabled. I've also added the Developer tab to my ribbon. I named the Drop-Down RAG (for Red Amber Green)

I see the VBA on the ribbon and I open it up and cut and paste the code into the window and I get nothing.

I suspect I'm missing a step, or five.

So assume that I don't want to learn how to do this, but that I just need to shove this in there and move on with my life. How do I do that?

Thanks in advance!

I'm also open to non-VBA solutions!

posted by Ruthless Bunny to Computers & Internet (4 answers total)

I don't know how you're planning to use this document so I'm not sure if this would work for your purposes, but could you set up the conditional formatting in Excel, copy those cells, and paste special as an Excel object? Then you can double click on it in the Word document and if you enter a value in one of the cells it'll change there too.
posted by Mrs. Pterodactyl at 9:36 AM on March 10, 2016 [1 favorite]

Response by poster: I don't know how you're planning to use this document so I'm not sure if this would work for your purposes, but could you set up the conditional formatting in Excel, copy those cells, and paste special as an Excel object? Then you can double click on it in the Word document and if you enter a value in one of the cells it'll change there too.

I gave it a shot, but the selection is in the Word Doc, not Excel, so the functionality doesn't transfer.
posted by Ruthless Bunny at 11:00 AM on March 10, 2016

OK, 1. Make sure when you're cutting and pasting the code, the item highlighted to the left is "ThisDocument". That's the window in which you want to put the code.

2. The code as you had it didn't work for me, but this did:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
If ContentControl.Title = "RAG" Then
With ContentControl.Range
Select Case .Text
Case "Red"
.Shading.BackgroundPatternColor = wdColorRed
Case "Yellow"
.Shading.BackgroundPatternColor = wdColorYellow
Case "Green"
.Shading.BackgroundPatternColor = wdColorGreen
End Select
End With
End If
End Sub

Once you've copied/pasted the code, at the top of that window choose debug > compile project.

Close window. You're now back to your Word doc and it should work (upon exiting the drop-down box).
posted by three easy payments and one complicated payment at 12:01 PM on March 10, 2016

Response by poster: Weird, I tried that, using ThisDocument and it's still not working.

I so appreciate your help!
posted by Ruthless Bunny at 2:05 PM on March 10, 2016