Microsoft Office Forums - View Single Post - Change Text Colour/Box Shade if CheckBox is marked

cavals07 · 01-30-2023, 11:05 AM

Quote:

Originally Posted by Guessed

Whilst it is laudable that you want to modularise the code, you are not ready to deal with those extra complexities. Also, you only get one bite of the event Document_ContentControlOnExit unless you go down the rabbit hole of Class Modules. The sensible way to make 'separate codes' would be to create functions where you pass across the variables. That would allow you to efficiently deal with the other sorts of edge cases that might be required (eg what if the content control for 'Type' isn't in a table cell).
In any event, your code is not sufficiently complex to require multiple Subs and trying to do so before you have a good grasp of the basics is expending effort in the wrong direction.

Code:

Private Sub Document_ContentControlOnExit(ByVal aCC As ContentControl, Cancel As Boolean)
  Select Case aCC.Title
    Case "Awaiting Response", "Response Received"
      If aCC.Checked = True Then
        aCC.Range.Paragraphs(1).Range.Font.ColorIndex = wdRed
      Else
        aCC.Range.Paragraphs(1).Range.Font.ColorIndex = wdBlack
      End If
    Case "Type"
      Select Case aCC.Range.Text
        Case "NCR"
          aCC.Range.Cells(1).Shading.BackgroundPatternColor = RGB(214, 227, 188)
        Case "CAR"
          aCC.Range.Cells(1).Shading.BackgroundPatternColor = RGB(182, 221, 232)
        Case "OFI"
          aCC.Range.Cells(1).Shading.BackgroundPatternColor = RGB(251, 212, 180)
        Case Else
          aCC.Range.Cells(1).Shading.BackgroundPatternColor = RGB(255, 255, 255)
      End Select
  End Select
End Sub

Thank you Andrew - for all your time and support. Your code is great, and I will continue to work on honing my skills in VBA. All the best to my fellow VBA users, I look forward to keeping involved in this community to progress with you all!