Microsoft Office Forums - View Single Post - [Solved] Change color according with dropdown selection

Kimberly10 · 08-12-2014, 12:25 PM

I am trying to do the same thing and have been successful in getting my drop down boxes to work. However, I need my document to be protected.

Below is what i have written. I can get it to unprotect prior to changing the background color but then the entire document is unprotected.

Code:

Option Explicit 
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean) 
    Dim StrPwd As String 
    StrPwd = "Password" 
    With ContentControl 
        If Len(.Title) < 4 Then Exit Sub 
        If Left(.Title, 4) = "SHR1" Then 
            If ActiveDocument.ProtectionType <> wdNoProtection Then 
                ActiveDocument.UnProtect Password:=StrPwd 
            End If 
            Select Case .Range.Text 
            Case "Red": .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdRed 
            Case "Yellow": .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdYellow 
            Case "Green": .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdBrightGreen 
            Case Else: .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight 
            End Select 
        End If 
        If Left(.Title, 4) = "SHR2" Then 
            If ActiveDocument.ProtectionType <> wdNoProtection Then 
                ActiveDocument.UnProtect Password:=StrPwd 
            End If 
            Select Case .Range.Text 
            Case "Red": .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdRed 
            Case "Yellow": .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdYellow 
            Case "Green": .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdBrightGreen 
            Case Else: .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight 
            End Select 
        End If 
    End With 
End Sub