View Single Post
 
Old 03-14-2018, 11:04 AM
anakuprava anakuprava is offline Windows 10 Office 2016
Novice
 
Join Date: Mar 2018
Posts: 4
anakuprava is on a distinguished road
Default

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