Change color according with dropdown selection

Kimberly10 · #10 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
block selection in dropdown list	Intruder	Excel	2	01-10-2013 10:20 AM
Dropdown selection value	coconutt	Word VBA	5	09-13-2012 05:23 PM
Change cell color when selection is made from a drop down list	fedcco	Excel	12	08-28-2012 10:43 PM
Autofill a form which is contingent on a dropdown selection.	biffle0764	Word	2	05-09-2012 12:54 PM
Change cell color everytime a value is selected in dropdown list	angelica_gloria	Excel	4	01-27-2012 06:47 PM