Unable to abbreviate "Strong"

kevinbradley57 · #1 05-04-2023, 12:12 PM

I have a dropdown content control in a table cell. The font and background color change based on the selection made. One of the dropdown options is "Strong" and that works fine. I have a request to abbreviate it as "Str" but when I make that change in the VB and in the dropdown, either the background and font colors are both white or it's returning a blank cell (I can't tell which).

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
With ContentControl
      Case "CE"
      Select Case .Range.Text
        Case "Strong": .Range.Cells(1).Shading.BackgroundPatternColor = 6299648: .Range.Cells(1).Range.Font.ColorIndex = wdWhite
        Case "Weak": .Range.Cells(1).Shading.BackgroundPatternColor = 4739264: .Range.Cells(1).Range.Font.ColorIndex = wdWhite
        Case "Sat": .Range.Cells(1).Shading.BackgroundPatternColor = 5880731: .Range.Cells(1).Range.Font.ColorIndex = wdBlack
        Case "IN": .Range.Cells(1).Shading.BackgroundPatternColor = 5232127: .Range.Cells(1).Range.Font.ColorIndex = wdBlack
        Case Else: .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight
      End Select
    Case Else
  End Select
End With
End Sub

Guessed · #2 05-04-2023, 03:47 PM

Your code is non-functioning. If I correct the missing line to allow it to work we can also allow two options for Str or Strong

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
  With ContentControl
    Select Case .Title
        Case "CE"
          Select Case .Range.Text
            Case "Strong", "Str": .Range.Cells(1).Shading.BackgroundPatternColor = 6299648: .Range.Cells(1).Range.Font.ColorIndex = wdWhite
            Case "Weak": .Range.Cells(1).Shading.BackgroundPatternColor = 4739264: .Range.Cells(1).Range.Font.ColorIndex = wdWhite
            Case "Sat": .Range.Cells(1).Shading.BackgroundPatternColor = 5880731: .Range.Cells(1).Range.Font.ColorIndex = wdBlack
            Case "IN": .Range.Cells(1).Shading.BackgroundPatternColor = 5232127: .Range.Cells(1).Range.Font.ColorIndex = wdBlack
            Case Else: .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight
          End Select
    End Select
  End With
End Sub

kevinbradley57 · #3 05-04-2023, 05:12 PM

Andrew - Thanks for the quick response. I applied your suggestions but the colors only work right when "Strong" is selected. Selecting "Str" does not work. If you have time, the attached file is a one-page Word doc with the problematic table. The blank cell (bottom right) should be showing "Str" with white font on a dark blue back ground.

Guessed · #4 05-04-2023, 07:30 PM

The code in your attached document hasn't got a mention of "Str" so the result will be the Case Else is the line making changes. In that line you set the background color but don't touch the font color so if it was white before you got there it is now white font on a white background.

If you want Str to be treated the same as Strong, include it in the Case condition like I did

If you want Case Else to set the background AND the font then change that line to

Code:

Case Else: .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight: .Range.Cells(1).Range.Font.ColorIndex = wdBlack

kevinbradley57 · #5 05-05-2023, 07:32 AM

Andrew - Thanks for your assistance. Much appreciated.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel 2003: VBA "Function" causes "#VALUE!" errors after running "insert/delete row" custom macro	Matt C	Excel Programming	2	01-08-2022 06:03 AM
Unable to customize ribbon, remove "Cell Styles"	pjs15	Excel	1	08-21-2020 08:01 AM
When applying styles word is automatically reverting to "strong" style partway through my document	Victoria S	Word	3	11-17-2015 12:51 PM
Message "Unable to read file" when clicking on the Excel icon	roundman	Excel	3	12-03-2014 04:28 PM
Unable to "Shift cells down" on unprotected area in a protected worksheet	ricky0309	Excel	0	08-14-2014 01:51 AM