Change Text Colour/Box Shade if CheckBox is marked

cavals07 · #1 01-26-2023, 10:52 AM

Hi all!

Thanks for welcoming me into this community. I am a basic VBA user (very beginner!) and hoping you may be able to help me out.

I've been trying (embarassingly for hours) to create a VBA code that changes the text colour/check box colour/background shading to RED if the 'AWAITING RESPONSE' is checked, and to GREEN if the 'RESPONSE RECEIVED' is checked.

I have attached a sample doc

This is what I currently have:

Private Sub Module1_ContentControlOnEnter(ByVal ContentControl As ContentControl, Cancel As Boolean)

If (ContentControl.Title = "Awaiting Response" And ContentControl.Checked = True) Then
ContentControl.Range.Font.ColorIndex = wdRed

End If
If (ContentControl.Title = "Awaiting Response" And ContentControl.Checked = False) Then
ContentControl.Range.Font.ColorIndex = wdGreen
End If
End Sub

Any help would be super appreciated!!

Guessed · #2 01-26-2023, 02:39 PM

On Enter means the macro is running before you have made your selection. It would be more logical to run it after the CC value has been changed.

Based on the name of your macro, perhaps you are not aware that it only works when it has the right name AND the code is placed in the ThisDocument module.

Try this code

Code:

Private Sub Document_ContentControlOnExit(ByVal aCC As ContentControl, Cancel As Boolean)
  If aCC.Title = "Awaiting Response" Then
    If aCC.Checked = True Then
      aCC.Range.Paragraphs(1).Range.Font.ColorIndex = wdRed
    Else
      aCC.Range.Paragraphs(1).Range.Font.ColorIndex = wdGreen
    End If
  End If
End Sub

cavals07 · #3 01-26-2023, 05:39 PM

Hi Andrew, holy moly yes it works perfectly. Just what I am hoping for! Thank you so much for revising my code for me and for the additional information regarding the requirement for same name and placement within the ThisDocument.

If I wanted to use that code a second time in the same macro (ie. to have second the "Response Received" checkbox change its text colour, can you advise me what I need to include in between the two codes to make that work?

When I copy and paste the code back-to-back and only revise "Awaiting Response" to "Response Received", it prompts me with an error as followed: "Compile error: Ambiguous name detected: Document_ContentControlOnExit"

Code:

Private Sub Document_ContentControlOnExit(ByVal aCC As ContentControl, Cancel As Boolean)
  If aCC.Title = "Awaiting Response" Then
    If aCC.Checked = True Then
      aCC.Range.Paragraphs(1).Range.Font.ColorIndex = wdRed
    Else
      aCC.Range.Paragraphs(1).Range.Font.ColorIndex = wdGreen
    End If
  End If
End Sub

Private Sub Document_ContentControlOnExit(ByVal aCC As ContentControl, Cancel As Boolean)
  If aCC.Title = "Response Received" Then
    If aCC.Checked = True Then
      aCC.Range.Paragraphs(1).Range.Font.ColorIndex = wdRed
    Else
      aCC.Range.Paragraphs(1).Range.Font.ColorIndex = wdGreen
    End If
  End If
End Sub

Guessed · #4 01-26-2023, 06:39 PM

It all has to get crammed into one macro - you can't have the same macro name in the same module.

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 = wdGreen
      End If
  End Select
End Sub

cavals07 · #5 01-26-2023, 09:38 PM

Quote:

Originally Posted by Guessed

It all has to get crammed into one macro - you can't have the same macro name in the same module.

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 = wdGreen
      End If
  End Select
End Sub

Thank you so much Andrew! You have been a true saviour - I appreciate your guidance so very much!! Cheers!! I have learned lots from you.

cavals07 · #6 01-27-2023, 11:41 AM

Quote:

Originally Posted by cavals07

Thank you so much Andrew! You have been a true saviour - I appreciate your guidance so very much!! Cheers!! I have learned lots from you.

Hi all again! I have been toying around with the attached form (expanded on it to try to challenge my VBA skills). And have successfully created Code 2 which changes the text box colour based on which drop down option is chosen. However, I am having difficulties combining Code 1 (the one Andrew graciously revised for me) with Code 2 so that both codes work in the same document.

I have tried my hand at both a) combining the code (clearly I am making mistakes) as well as b) trying to create separate Modules with separate names. No luck on both fronts. Is there any suggestion as to how I can accomplish this please and thanks?

I really love challenging myself with VBA, but boy is it demoralizing at times! Any assistance or links to resources would be very appreciated.! Ideally, I would love to be able to create separate codes that I can keep in their own separate modules, rather than combining the code into one macro (makes it much more difficult for me to try to write the code - less streamlined, more complicated for a beginner VBA-er IMO)

Code 1:

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
  End Select
End Sub

Code 2:

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
With ContentControl.Range
    If ContentControl.Title = "Type" Then
        Select Case .Text
            Case "NCR"
                .Cells(1).Shading.BackgroundPatternColor = RGB(214, 227, 188)
            Case "CAR"
                .Cells(1).Shading.BackgroundPatternColor = RGB(182, 221, 232)
            Case "OFI"
                .Cells(1).Shading.BackgroundPatternColor = RGB(251, 212, 180)
 
        End Select
    End If
End With
End Sub

Guessed · #7 01-28-2023, 02:27 PM

Quote:

I would love to be able to create separate codes that I can keep in their own separate modules, rather than combining the code into one macro (makes it much more difficult for me to try to write the code - less streamlined, more complicated for a beginner VBA-er IMO)

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

cavals07 · #8 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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Conditionally Colour/Shade Numeric Output Ranges	E.Buttimer	Mail Merge	1	03-25-2021 02:20 PM
Change text colour for content control labels?	Toe	Word	1	01-17-2019 08:45 AM
Change Text Colour in Cell Based on Text in Same Cell	PMC11	Word VBA	1	11-14-2017 09:15 PM
Quickest way to change text to Arial size 11 specific colour	BlueClearSky	Word	6	11-22-2013 03:34 PM
How to change line height for marked text (in Word 2007)? ... as default for font?	pstein	Word	1	01-14-2012 10:15 AM