Thank you gmaxey and macropod, your recommendations worked! I have several drop-downs like these within document and all of them seem to be working except for the two options ("Unsatisfactory" and "Adequate" within the case "Rating"). I want "Unsatisfactory" table fill to be red and "Adequate" yellow, but when I select those options the table stays blank. Could you please take a look at the code one more time and maybe there's something I'm missing with regards to these two options?
And another issue I have now is that these drop-downs are meant to be part of the word template with other elements (texts, tables etc.) and after I added drop-downs and respective VBA code the rest of the document is sort of "frozen". I can not edit/type in anything else in the file. Could you suggest how to deal with this?
Thanks
Code:
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Const StrPwd As String = "abc"
With ContentControl
Select Case .Title
Case "Finding"
If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd
With .Range
Select Case .Text
Case "A"
.Cells(1).Shading.BackgroundPatternColorIndex = wdRed
.Font.ColorIndex = wdAuto
Case "B"
.Cells(1).Shading.BackgroundPatternColor = RGB(255, 153, 0)
.Font.ColorIndex = wdAuto
Case "C"
.Cells(1).Shading.BackgroundPatternColorIndex = wdYellow
.Font.ColorIndex = wdAuto
Case Else
.Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight
.Font.ColorIndex = wdAuto
End Select
End With
ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd
End Select
Select Case .Title
Case "Rating"
If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd
With .Range
Select Case .Text
Case "Satisfactory"
.Cells(1).Shading.BackgroundPatternColorIndex = wdBrightGreen
.Font.ColorIndex = wdAuto
Case "Adequate"
.Cells(1).Shading.BackgroundPatternColorIndex = wdBrightYellow
.Font.ColorIndex = wdAuto
Case "Requires Improvement"
.Cells(1).Shading.BackgroundPatternColor = RGB(255, 153, 0)
.Font.ColorIndex = wdAuto
Case "Unsatisfactory"
.Cells(1).Shading.BackgroundPatternColorIndex = Red
.Font.ColorIndex = wdAuto
Case Else
.Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight
.Font.ColorIndex = wdAuto
End Select
End With
ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd
End Select
Select Case .Title
Case "Schedule"
If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd
With .Range
Select Case .Text
Case "Yes"
.Cells(1).Shading.BackgroundPatternColorIndex = wdAuto
.Font.ColorIndex = wdAuto
Case "No"
.Cells(1).Shading.BackgroundPatternColorIndex = wdAuto
.Font.ColorIndex = wdRed
Case Else
.Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight
.Font.ColorIndex = wdAuto
End Select
End With
ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd
End Select
Select Case .Title
Case "GM"
If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd
With .Range
Select Case .Text
Case "Higher than ORA"
.Cells(1).Shading.BackgroundPatternColorIndex = wdAuto
.Font.ColorIndex = wdAuto
Case "Lower than ORA"
.Cells(1).Shading.BackgroundPatternColorIndex = wdAuto
.Font.ColorIndex = wdRed
Case Else
.Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight
.Font.ColorIndex = wdAuto
End Select
End With
ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd
End Select
Select Case .Title
Case "Cash"
If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd
With .Range
Select Case .Text
Case "Positive"
.Cells(1).Shading.BackgroundPatternColorIndex = wdAuto
.Font.ColorIndex = wdAuto
Case "Negative"
.Cells(1).Shading.BackgroundPatternColorIndex = wdAuto
.Font.ColorIndex = wdRed
Case Else
.Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight
.Font.ColorIndex = wdAuto
End Select
End With
ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd
End Select
End With
End Sub