#1
|
|||
|
|||
Change color according with dropdown selection
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! |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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?... |
#4
|
||||
|
||||
"ColorPicker" is the name of the formfield's bookmark. You set it via the formfield's properties.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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! |
#6
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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?
|
#9
|
||||
|
||||
Quote:
Quote:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
|||
|
|||
Use drop down when form is protected
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 Last edited by macropod; 08-12-2014 at 02:55 PM. Reason: Added code tags & formatting |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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. |
#13
|
|||
|
|||
Reply
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 |
#14
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
|||
|
|||
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 Last edited by macropod; 09-10-2018 at 02:50 PM. Reason: Added code tags & formatting |
Tags |
color, dropdown, reaction |
|
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 |