Change color according with dropdown selection

spk · #1 04-03-2013, 01:40 AM

Hello,

I'm doing a document with dropdown menus, and I'd like to insert a color code for easy reading.
These dropdows are in a table, so ideally, the the color would apply to the cell background.
The document doesn't need to be protected.

For instance, I've a dropdown named "Material" in a cell of a tab, with two entries: "mat1" and "mat2".
I would like to color the cell background in light blue when "mat1" is selected, and in light green when "mat2" is selected.

I found this:
http://www.wordbanter.com/showthread.php?t=67444

But I don't manage to use it, I get an error run-time '5941' the requested member of the collection does not exist.
The problematic line is:
Select Case ActiveDocument.FormFields("ColorPicker").Result
I replaced "ColorPicker" by the name of my dropdown, I put my dropdown in a bookmark with the same name...

If it works, we would save time and probably avoid mistakes!

macropod · #2 04-04-2013, 03:29 AM

Try the following, which is based on the code in your link:

Code:

Sub OnExitDD()
Dim StrRslt As String, StrPwd As String
Dim lRow As Long, lCol As Long
lRow = 1: lCol = 1: StrPwd = "Password"
With ActiveDocument
  If .ProtectionType = wdAllowOnlyFormFields Then .Unprotect , StrPwd
  StrRslt = .FormFields("ColorPicker").Result
  With .Tables(1).Cell(lRow, lCol).Range
    Select Case StrRslt
      Case "Red"
        .Font.Color = wdColorDarkRed
        .Shading.BackgroundPatternColor = wdColorPink
      Case "Blue"
        .Font.Color = wdColorDarkBlue
        .Shading.BackgroundPatternColor = wdColorLightBlue
      Case "Green"
        .Font.Color = wdColorDarkGreen
        .Shading.BackgroundPatternColor = wdColorBrightGreen
      Case Else
      'Do Nothing
    End Select
  End With
  .Protect wdAllowOnlyFormFields, True, NoReset:=True, Password:=StrPwd
End With
End Sub

Note: The code is designed to update the colours of the first cell in the first table in the document. You can change the table & cell references via the various 1s in the code.

spk · #3 04-04-2013, 12:21 PM

Thanks for your help macropod!

But unfortunately I get the same error, so I think the problem is not the code, but the way it's linked to my dropdown.

Does "ColorPicker" should be the title of the dropdown, or its tag, or the name of a bookmark?...

macropod · #4 04-04-2013, 02:16 PM

Quote:

Originally Posted by spk

But unfortunately I get the same error, so I think the problem is not the code, but the way it's linked to my dropdown.

Does "ColorPicker" should be the title of the dropdown, or its tag, or the name of a bookmark?...

"ColorPicker" is the name of the formfield's bookmark. You set it via the formfield's properties.

spk · #5 04-10-2013, 03:03 AM

Hello,
I think I found the problem: there are 2 ways to insert a dropdown, and they don't have the same properties.

Here are the 2 properties windows:
http://static.ddmcdn.com/gif/dropdown-form-field-2.jpg
http://www.79xperts.com/blog/wp-cont...creenshot4.jpg

So now I can set the bookmark in the dropdown properties.
As soon as I have time to re-try the code of @macropod, I'll give news.

Thank you again!

macropod · #6 04-10-2013, 03:32 AM

Hi spk,

In your original post, you included a link for working with formfields. Even the 'problematic' line you referred to mentions them. Given that, and your own references to bookmarks, I concluded you were working with formfields. The dropdowns you're having issues with, though, are content controls. Content controls are entirely different from formfields and don't have a bookmark property.

For demonstrations of dropdown formfield and dropdown content control implementation, see the attached.

Note 1: There are two versions for formfields - one requiring macros (the .docm file), the other not requiring them (the .docx file). The content control version also requires a macro. The macros are already present in the files concerned. Press Alt-F11 to see the VBA code. For the formfield versions, unprotect the document then press Alt-F9 to see the field coding.

Note 2: Formfields and content controls should not be used in the same document; they are not designed to work together.

For PC macro installation & usage instructions, see: Installing Macros
For Mac macro installation & usage instructions, see: Word:mac - Install a Macro

spk · #7 04-10-2013, 01:50 PM

Wow, it works perfectly!
I didn't know about content controls, but at the end I managed to create different ones based on your model.
Thank you so much

ejungk99 · #8 06-16-2014, 05:14 PM

Hi Paul, was looking at your "content control conditional formatting" file and it doesn't seem to color the cell after the selection. Also after selecting one of the dropdowns I keep getting an error message in VBA "ambiguous name detected". I'm sure this is some error on my end but do you have any suggestions?

macropod · #9 06-16-2014, 05:19 PM

Quote:

Originally Posted by ejungk99

Hi Paul, was looking at your "content control conditional formatting" file and it doesn't seem to color the cell after the selection.

The macro only colours the cell containing the content control - because that's all it was written to do - and is only activated once the content control is exited.

Quote:

Also after selecting one of the dropdowns I keep getting an error message in VBA "ambiguous name detected". I'm sure this is some error on my end but do you have any suggestions?

That suggests you have two or more macros with the same name, in which case you're using more than just the conditional format document as provided.

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

gmaxey · #11 08-12-2014, 12:37 PM

So protect it:

Code:

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
    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
    ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd
  End If
  If Left(.Title, 4) = "SHR2" Then
    If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd
    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
    ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd
  End If
End With
End Sub

I've assumed that you are using protection for formfields which begs the question "why?"

Kimberly10 · #12 08-12-2014, 12:47 PM

I am using it to protect the entire document i.e. other aspects of the document that I do not want changed.
I copied your code and I am gettin an error: Run-time error '4605': This method or property is not available because the object refers to a protected area of the document.

gmaxey · #13 08-12-2014, 01:00 PM

The code runs here without error:

Is your password "Password"

Unless you are using formfields, there is no point in protecting for filling in forms. See: http://gregmaxey.com/word_tip_pages/...llin_form.html and the section "Restrict Editing"

Code:

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
    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
    ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd
  End If
  If Left(.Title, 4) = "SHR2" Then
    If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd
    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
    ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd
  End If
End With
End Sub

macropod · #14 08-13-2014, 07:44 PM

Quote:

Originally Posted by gmaxey

Unless you are using formfields, there is no point in protecting for filling in forms.

That's not the only useful scenario. It functions the same way with content controls, too (i.e. they remain accessible, but the remainder of the document is inaccessible). That may suit the designer's purposes better than read-only restrictions with a plethora of exceptions.

palmiema · #15 08-24-2016, 01:39 PM

Hello

I don't know if this thread is still active, but if so it addresses my topic exactly. I have a Word 2010 table with a drop down list (created by going to Developer tab => Drop-Down List Content Control, and then Properties). I don’t know if this means I am using form fields or not. I would like the cell background color (and font) to change based on the drop-down selection. I am using the code below, which is adapted from the code in the thread above. I don’t get an error message, but the cell background color doesn’t change either. I have editing restrictions enabled, and have highlighted the cells in the table that contain the drop-down list and allowed others to modify those cells. Any thoughts regarding how to make the code work so that the background (and font) change with the drop-down selection are much appreciated.

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim StrPwd As String
StrPwd = "DocumentPassword"
With ContentControl
  If Len(.Title) < 4 Then Exit Sub
  If Left(.Title, 4) = "Lst1" Then
    If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd
    Select Case .Range.Text
      Case "High": .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdRed
      Case "Medium": .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdYellow
      Case "Low": .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdBrightGreen
      Case Else: .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight
    End Select
    ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd
  End If
  If Left(.Title, 4) = "Lst2" Then
    If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd
    Select Case .Range.Text
      Case "High": .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdRed
      Case "Medium": .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdYellow
      Case "Low": .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdBrightGreen
      Case Else: .Range.Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight
    End Select
    ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd
  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