Run macro when drop-down list changes

Question

Hi - I have a form containing a table with multiple rows. Within each row is a multi-option dropdown to select an option for each customer. I'd like the to use a macro to change the background colour of the row depending on the option selected.

Is that possible with a single macro? I don't want to have to have one for each dropdown and have to reference the dropdown name.

Thanks

Simon

Answer 1

A multi-option dropdown ...

Well, to start, we would have to know what type of multi-option dropdown. If it is a legacy formfield dropdown, we would go one way. If it is a ContentControl dropdown (which it should be) we would go another way.

Let's assume that it is what it should be and you are using content controls in your form. Here again, we have two paths. One is to use the Document_ContentControlOnExit event. This is very simple, but the drawback is the user must make their selection and then "Exit' the content control. If they just make the selection, save and close or save and print then the shading will not reflect the selection. Stupidly, Microsoft has never finished what they started with ContentControls and provided a "Change" event.

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
  If ContentControl.Type = wdContentControlDropdownList Then
    Select Case ContentControl.Range.Text
      Case "A": ContentControl.Range.Rows(1).Shading.BackgroundPatternColor = wdColorBlue
      Case "B": ContentControl.Range.Rows(1).Shading.BackgroundPatternColor = wdColorRed
      Case "C": ContentControl.Range.Rows(1).Shading.BackgroundPatternColor = wdColorLightOrange
    End Select
  End If
lbl_Exit:
  Exit Sub
End Sub

With considerable more effort, you can create a pseudo change event with content control dropdown list and checkboxes. To do this, you: 1) map your content controls to a CustomXMLPart, 2) Initialize an Event Monitor for the CustomXMLPart 3) Utilize the oCXMLPart_NodeAfterReplace event as a pseudo ContentControl change event.

Option Explicit

Dim WithEvents oCXMLPart As CustomXMLPart

Dim m_oRow As Row

Sub AutoOpen()

InitializeCPEventMonitor

lbl_Exit:

Exit Sub

End Sub

Sub InitializeCPEventMonitor()

Dim CPart As CustomXMLPart

For Each CPart In ActiveDocument.CustomXMLParts

 If CPart.NamespaceURI = "[http://TheAnchorage/DataXMLPart](http://TheAnchorage/DataXMLPart "theanchorage")" Then

   Set oCXMLPart = CPart

   Exit For

 End If

Next

lbl_Exit:

Exit Sub

End Sub

Private Sub Document_ContentControlOnEnter(ByVal ContentControl As ContentControl)

On Error Resume Next

Set m_oRow = ContentControl.Range.Rows(1)

If Err.Number <> 0 Then Set m_oRow = Nothing

lbl_Exit:

Exit Sub

End Sub

Private Sub oCXMLPart_NodeAfterReplace(ByVal OldNode As Office.CustomXMLNode, ByVal NewNode As Office.CustomXMLNode, ByVal InUndoRedo As Boolean)

'This event fires when a CP text node is replaced with a new text node value.

ProcessChange NewNode

lbl_Exit:

Exit Sub

End Sub

Sub ProcessChange(oNodePassed As Office.CustomXMLNode)

Select Case oNodePassed.Text

Case "A": m\_oRow.Shading.BackgroundPatternColor = wdColorBlue

Case "B": m\_oRow.Shading.BackgroundPatternColor = wdColorRed 

Case "C": m\_oRow.Shading.BackgroundPatternColor = wdColorLightOrange

End Select

lbl_Exit:

Exit Sub

End Sub

As you see, this is not a walk in the sun. You would have to modify this code example to match your selections and to match the namespace of the CustomXMLPart you create and use to map the CCs.

Answer 2

Simon,

I have been out all day. Sorry.

The code goes in the ThisDocument module. That is where Document events are located.

If you are using the simple method, then you will need to add the ComboBox type to the If expression.

Answer 3

Hi Simon,

Possible? Yes.

Simple? No.

It is much easier to do this in Excel than in Word

I am assuming you are using Content Controls, not legacy FormFields.

Set paragraph styles for your various shading options to incorporate in the macro.

See the following pages for help with a macro to run upon exit from a Content Control:

• Content Control Custom Events by Greg Maxey
• Link "Conditional Content" to a List Selection

[Edit: This was posted before I saw Greg's response.]

Answer 4

That's worked a treat, thank you. If I wanted to potentially change the font colour of the cells within the row, is that possible as well?

Thanks

Simon

Answer 5

Thank you Greg, that's really helpful. I am using the Combo Box Content Controls.

Dumb question! How do I get the Combo Box tied to the event? I've added the code but it doesn't seem to fire. I can't see anywhere to set the properties to fire the event.

Thanks

Simon