Code to format cell background based on drop down result

[bm85]

Good Afternoon,

I currently have this query i need help with in word:

I need to change the background fill colour of a cell based on the selection from the drop down in the same cell (cell is row 3, column 4).
At the moment I have four selections (Pass, Fail, Skip, Block) and need a different colour for each selection.

If someone could give me some pointers it would be very much appreciated. I do have some knowledge of VBA, but it's mainly in Excel.

Thanks

Brad

[Frosty]

Why don't you try recording a macro in Word to do what you want to do?

If you're familiar with Excel, you should maybe do it in excel.

The differences in actually applying the color between the applications are minimal, and you would easily be able to see them if you recorded the macro in excel vs. word (mostly just to get the actual values of the 4 colors you want to do).

But I'm unclear if you have the bigger architecture set up: a menu with 4 different selections, I'm not clear if that is a right-click command bar, a command bar in versions of word 2003 and previous, a custom button on a ribbon control in Word 2007 or 2010... perhaps you can post a bit of your sample code or be a little more clear on what you want to do, where you start and where you want to end up?

[bm85]

Hi Frosty,

Thanks for the reply.

Unfortunately this has to be done in Word, as it has to be .doc(x) format for uploading to a log.

I have attached a screenshot showing the table in word and the drop down with the four options. I need to be able to have the background colour for the cell containing the drop down change depending on which of the 4 options are selected from the list.

Hope that makes sense.

Regards

Brad

[Frosty]

I meant set up your coding in Excel, if that is the variation of VB you are most familiar with, since the actual code to do it won't be that different except for the actual "shade this word cell" vs. "shade this excel cell" code.

Instead of a screen shot, perhaps you could just attach the .docm you're working on with that table setup and that drop down. I don't really want to write the whole code project for you, but specific questions are easier to answer. It looks like you're attempting to use a content control, which I'm not terribly familiar with. But show how far you've gotten and it will be easier to help out.

[Frosty]

Someone else may have an idea about binding vba code to a content control, but my bit of research shows that it doesn't seem possible to do what you're trying to do (i.e., pick one of four options from a content control, and then automatically have the cell behind that (or the entire row) change background shading). I.e., the concept of conditional formatting in Excel (which I think you only get 3 options for anyway).

My guess is that you'll need to tackle the problem a different way. Here are some brainstorms:

1. modify the right-click menu which shows up when you right-click in a table cell, to give you the options to run 1 of 4 macros (each of which performs a two actions: change the text of the cell to "Pass" (or whatever) and the shading of the cell to "Blue" (or whatever)

2. Have a single macro which operates on your selected cell/row/column and changes the shading to 1 of 4 options, as well as the text of each cell in the selection (you can start to tackle this just by recording a macro where you Find the text, then change the shading of the cell manually).

3. A better description of the "Big Picture" of what you want to do, and perhaps a "Format my entire table" macro would be more useful.

I don't think you want to use a macro button, since that button would live there.

However, I'm no expert on the abilities of content controls, so perhaps there is actually a way to do what you're trying to do... but I would probably suggest trying #3 and we'll try to go from there.

[gmaxey]

I think the only way to do that real time (i.e., as the selections are made) is to map the content control to a CustomXMLPart data node and then set up a a WithEvents procedure to monitor the node data changes:

Option Explicit
Dim WithEvents oMonitor As CustomXMLPart
Sub AutoOpen()
On Error Resume Next
Set oMonitor = ThisDocument.CustomXMLParts(4)
End Sub
Private Sub oMonitor_NodeAfterReplace(ByVal OldNode As Office.CustomXMLNode, ByVal NewNode As Office.CustomXMLNode, ByVal InUndoRedo As Boolean)
Dim oCell As Cell
Set oCell = Selection.Cells(1)
Select Case NewNode.ParentNode.BaseName
Case "CCMapChild_DemoCCII"
Select Case oMonitor.SelectSingleNode("/ns0:ccMap[1]/ns0:CCMapChild_DemoCCII[1]").Text
Case "Pass"
oCell.Shading.BackgroundPatternColorIndex = wdBrightGreen
Case "Fail"
oCell.Shading.BackgroundPatternColorIndex = wdRed
Case "Skip"
oCell.Shading.BackgroundPatternColorIndex = wdBlue
Case "Block"
oCell.Shading.BackgroundPatternColorIndex = wdPink
Case Else
oCell.Shading.BackgroundPatternColorIndex = wdAuto
End Select
End Select
End Sub















Hi Frosty,

Thanks for the reply.

Unfortunately this has to be done in Word, as it has to be .doc(x) format for uploading to a log.

I have attached a screenshot showing the table in word and the drop down with the four options. I need to be able to have the background colour for the cell containing the drop down change depending on which of the 4 options are selected from the list.

Hope that makes sense.

Regards

Brad

[gmaxey]

Here is some example code that shows how to create and map a target content control:

Sub AddCCsAndMap()
Dim oCC As ContentControl
With ActiveDocument
Set oCC = .ContentControls.Add(Type:=wdContentControlDropdownList, Range:=ActiveDocument.Tables(1).Cell(3, 4).Range)
With oCC
.Title = "Result"
With .DropdownListEntries
.Add "Choose and item"
.Add "Pass"
.Add "Fail"
.Add "Skip"
.Add "Block"
End With
End With
End With
MapCCs
ThisDocument.AutoOpen
End Sub

Sub MapCCs()
Dim oCC As ContentControl
Dim pXML As String
Dim oCustXMLPart As CustomXMLPart
Dim XPath As String
pXML = "<?xml version='1.0' encoding='utf-8'?><Root><Result></Result></Root>"
ClearXMLParts
Set oCustXMLPart = ActiveDocument.CustomXMLParts.Add(pXML)
Set oCC = ActiveDocument.SelectContentControlsByTitle("Result").Item(1)
XPath = "/Root/Result[1]"
oCC.XMLMapping.SetMapping XPath
Set oCC = Nothing
Set oCustXMLPart = Nothing
End Sub

Sub ClearXMLParts()
Dim i As Long
For i = ActiveDocument.CustomXMLParts.Count To 4 Step -1
ActiveDocument.CustomXMLParts(i).Delete
Next i
End Sub


This code would be in a standard module. With the target CC mapped you would create and initiate a With Events monitor for the CustomXMLPart. This code would be in the ThisDocument module:

Option Explicit
Dim WithEvents oMonitor As CustomXMLPart
Sub AutoOpen()
On Error Resume Next
Set oMonitor = ThisDocument.CustomXMLParts(4)
End Sub
Private Sub oMonitor_NodeAfterReplace(ByVal OldNode As Office.CustomXMLNode, ByVal NewNode As Office.CustomXMLNode, ByVal InUndoRedo As Boolean)
Dim oCell As Cell
Set oCell = Selection.Cells(1)
Select Case NewNode.ParentNode.BaseName
Case "Result"
Select Case oMonitor.SelectSingleNode("/Root/Result[1]").Text
Case "Pass"
oCell.Shading.BackgroundPatternColorIndex = wdBrightGreen
Case "Fail"
oCell.Shading.BackgroundPatternColorIndex = wdRed
Case "Skip"
oCell.Shading.BackgroundPatternColorIndex = wdBlue
Case "Block"
oCell.Shading.BackgroundPatternColorIndex = wdPink
Case Else
oCell.Shading.BackgroundPatternColorIndex = wdAuto
End Select
End Select
End Sub





Hi Frosty,

Thanks for the reply.

Unfortunately this has to be done in Word, as it has to be .doc(x) format for uploading to a log.

I have attached a screenshot showing the table in word and the drop down with the four options. I need to be able to have the background colour for the cell containing the drop down change depending on which of the 4 options are selected from the list.

Hope that makes sense.

Regards

Brad

[Frosty]

Finally got to check that code out. That's great, Greg. Thanks for the education. It looks to me like it's not terribly expandable, is that accurate? It seems like I'd have to create a separate oMonitor for each content control?

[gmaxey]

Jason,

It is not "terribly" expandable but it is expandable and no you wouldn't have to create an event monitor for each control. To add and monitor a second control you would just have to map it to its own XML node. Something like this:

I manually added a second CC named "Test" with the following DD entries (One, Two, Three). I added a node to the XML in the MapCC procedure and ran it to map both CCs:

Sub MapCCs()
Dim oCC As ContentControl
Dim pXML As String
Dim oCustXMLPart As CustomXMLPart
Dim XPath As String
pXML = "<?xml version='1.0' encoding='utf-8'?><Root><Result></Result><Test></Test></Root>"
ClearXMLParts
Set oCustXMLPart = ActiveDocument.CustomXMLParts.Add(pXML)
Set oCC = ActiveDocument.SelectContentControlsByTitle("Result").Item(1)
XPath = "/Root/Result[1]"
oCC.XMLMapping.SetMapping XPath
Set oCC = ActiveDocument.SelectContentControlsByTitle("Test").Item(1)
XPath = "/Root/Test[1]"
oCC.XMLMapping.SetMapping XPath
Set oCC = Nothing
Set oCustXMLPart = Nothing
End Sub


Then modified the Monitor as follows:

Private Sub oMonitor_NodeAfterReplace(ByVal OldNode As Office.CustomXMLNode, ByVal NewNode As Office.CustomXMLNode, ByVal InUndoRedo As Boolean)
Dim oCell As Cell
Set oCell = Selection.Cells(1)
Select Case NewNode.ParentNode.BaseName
Case "Result"
Select Case oMonitor.SelectSingleNode("/Root/Result[1]").Text
Case "Pass"
oCell.Shading.BackgroundPatternColorIndex = wdBrightGreen
Case "Fail"
oCell.Shading.BackgroundPatternColorIndex = wdRed
Case "Skip"
oCell.Shading.BackgroundPatternColorIndex = wdBlue
Case "Block"
oCell.Shading.BackgroundPatternColorIndex = wdPink
Case Else
oCell.Shading.BackgroundPatternColorIndex = wdAuto
End Select
Case "Test"
Select Case oMonitor.SelectSingleNode("/Root/Test[1]").Text
Case "One"
oCell.Shading.BackgroundPatternColorIndex = wdBrightGreen
Case "Two"
oCell.Shading.BackgroundPatternColorIndex = wdRed
Case "Three"
oCell.Shading.BackgroundPatternColorIndex = wdBlue
Case Else
oCell.Shading.BackgroundPatternColorIndex = wdAuto
End Select
End Select
End Sub


I have looked at this in Word2010, but the limitation in Word2007 at least is that it won't work for plain text controls (i.e., it is not really a change event).