#16
|
||||
|
||||
Your description suggests you are using content controls, so that's OK. However, without actually seeing your document, it can be difficult for anyone to diagnose the issue. Can you attach a document to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#17
|
||||
|
||||
Setting the background colour relies heavily on the pattern you have applied to the cell. It would be simpler to set the foreground colour instead. Since the code chooses the cell based on the location of the CC, you can treat both CCs the same way
Code:
Private Sub Document_ContentControlOnExit(ByVal aCC As ContentControl, Cancel As Boolean) Dim StrPwd As String, iCol As Long StrPwd = "DocumentPassword" With aCC Select Case .Title Case "Lst1", "Lst2" 'MsgBox .Range.Text Select Case .Range.Text Case "High" iCol = wdRed Case "Medium" iCol = wdYellow Case "Low" iCol = wdBrightGreen Case Else iCol = wdWhite End Select If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd .Range.Cells(1).Shading.ForegroundPatternColorIndex = iCol ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd End Select End With End Sub
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#18
|
|||
|
|||
Thank you, macropod, for your help. I have attached an example document, which contains a sample document with a 4-level table of contents and editing restrictions. The table for which I am trying to color individual cells using a drop-down list is in section 1.7 on page 2. Ultimately, I want the cells completely filled with a solid color: red, yellow, or green, depending on the ranking of high, medium, or low, respectively. The words "high", "medium" or "low" do not need to appear in the cell, but if they do, I need to hide the letters by making them the same color as the rest of the cell. The password to modify the document is "ABC" and the password to remove the editing restrictions is "abc". These don't have to be different passwords, they can both be "ABC" or "abc" if that helps to solve the problem. Thanks again for your help, any thoughts you can provide are most appreciated. Please note, too, that the document is usually a .docx, but I saved the macro into the attached document as a .docm file. If there is any additional information I can provide to be helpful please let me know.
Update: It seems that the macro will work on my home laptop, but not on my work computer. Can the difference be that I have administrator status on my laptop but not on my work computer? |
#19
|
|||
|
|||
Thank you, Guessed, for your help. This code is more streamlined, and I tried to run it, but the cells were still not colored in the table. I am not sure why. I have attached a sample document in response to macropod's request. If you can take a look and offer any suggestions I'd very much appreciate your time and effort.
|
#20
|
||||
|
||||
Your sample doc is working on my machine without me having to change anything. The macro runs as the cursor moves out of the Content Controls in the section 1.7.
Are you sure you have enabled macros?
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#21
|
||||
|
||||
To colour the text & background so they're the same, you could use:
Code:
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean) Const StrPwd As String = "abc" With ContentControl Select Case .Title Case "Lst1", "Lst2" If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd With .Range Select Case .Text Case "High" .Cells(1).Shading.BackgroundPatternColorIndex = wdRed .Font.ColorIndex = wdRed Case "Medium" .Cells(1).Shading.BackgroundPatternColorIndex = wdYellow .Font.ColorIndex = wdYellow Case "Low" .Cells(1).Shading.BackgroundPatternColorIndex = wdBrightGreen .Font.ColorIndex = wdBrightGreen Case Else .Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight .Font.ColorIndex = wdAuto End Select End With ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd End Select End With End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#22
|
|||
|
|||
Thank you, Guessed! I had to add the macro to the specific document's macro module, instead of to the Normal Template's macro module...and then, success! Thanks for your help!
|
#23
|
|||
|
|||
Perfect! Thanks a lot for your help, macropod
|
#24
|
|||
|
|||
Ah! Paul/Macropod, please help! I've entered the code exactly as indicated in post #21 above, and the table pull-downs work great. But when I try to enter text into any other permitted content area in the document I keep getting routed back to the table. The pull-down list gets completed as the last step in the project, so I need to be able to fill in any and all of the rest of the document before choosing the pull-downs from the list. Can you offer advice? Example document attached. Thanks so much
|
#25
|
||||
|
||||
The macro only runs when you exit a content control titled "Lst1" or "Lst2", so you'd only run into problems if you had content controls other than your conditionally-shaded dropdowns using those titles.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#26
|
|||
|
|||
Hello
I am having the same problem as user Palmiema in previous posts and I can not figure out why the code I am using is not working. I have a Word 2016 table with a drop down list (created by going to Developer tab => Drop-Down List Content Control, and then Properties). I would like the cell background color to change based on the drop-down selection (A, B and C). I am using the code below, there might be some basic mistake that I am making, as I don't know how to work with macros and have copied the code from here and adopted to my file. I don’t get an error message, but the cell background color doesn’t change either. I have attached the word file as well and would appreciate your advise on how to make the code work so that the background change with the drop-down selections. Code:
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean) Const StrPwd As String = "abc" With ContentControl Select Case .Title Case "Rating" If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd With .Range Select Case .Text Case "A" .Cells(1).Shading.BackgroundPatternColorIndex = wdRed .Font.ColorIndex = wdRed Case "B" .Cells(1).Shading.BackgroundPatternColorIndex = wdYellow .Font.ColorIndex = wdOrange Case "C" .Cells(1).Shading.BackgroundPatternColorIndex = wdBrightGreen .Font.ColorIndex = wdYellow Case Else .Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight .Font.ColorIndex = wdAuto End Select End With ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd End Select End With End Sub |
#27
|
||||
|
||||
It's not surprising the code doesn't work; you've specified a ColorIndex that doesn't exist, namely wdOrange. You can't just go around making up your own ColorIndex names. Try using wdDarkYellow instead.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#28
|
|||
|
|||
Thank you, I changed it but it still does not seem to be working..
|
#29
|
|||
|
|||
With the exception of wdOrange which is still showing in the code above, your code works just fine. In the document you attached, there is no code.
The code you published (exception wdOrange) needs to go in the ThisDocument module of the project. |
#30
|
|||
|
|||
Thank you gmaxey and macropod, your recommendations worked! I have several drop-downs like these within document and all of them seem to be working except for the two options ("Unsatisfactory" and "Adequate" within the case "Rating"). I want "Unsatisfactory" table fill to be red and "Adequate" yellow, but when I select those options the table stays blank. Could you please take a look at the code one more time and maybe there's something I'm missing with regards to these two options?
And another issue I have now is that these drop-downs are meant to be part of the word template with other elements (texts, tables etc.) and after I added drop-downs and respective VBA code the rest of the document is sort of "frozen". I can not edit/type in anything else in the file. Could you suggest how to deal with this? Thanks Code:
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean) Const StrPwd As String = "abc" With ContentControl Select Case .Title Case "Finding" If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd With .Range Select Case .Text Case "A" .Cells(1).Shading.BackgroundPatternColorIndex = wdRed .Font.ColorIndex = wdAuto Case "B" .Cells(1).Shading.BackgroundPatternColor = RGB(255, 153, 0) .Font.ColorIndex = wdAuto Case "C" .Cells(1).Shading.BackgroundPatternColorIndex = wdYellow .Font.ColorIndex = wdAuto Case Else .Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight .Font.ColorIndex = wdAuto End Select End With ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd End Select Select Case .Title Case "Rating" If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd With .Range Select Case .Text Case "Satisfactory" .Cells(1).Shading.BackgroundPatternColorIndex = wdBrightGreen .Font.ColorIndex = wdAuto Case "Adequate" .Cells(1).Shading.BackgroundPatternColorIndex = wdBrightYellow .Font.ColorIndex = wdAuto Case "Requires Improvement" .Cells(1).Shading.BackgroundPatternColor = RGB(255, 153, 0) .Font.ColorIndex = wdAuto Case "Unsatisfactory" .Cells(1).Shading.BackgroundPatternColorIndex = Red .Font.ColorIndex = wdAuto Case Else .Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight .Font.ColorIndex = wdAuto End Select End With ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd End Select Select Case .Title Case "Schedule" If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd With .Range Select Case .Text Case "Yes" .Cells(1).Shading.BackgroundPatternColorIndex = wdAuto .Font.ColorIndex = wdAuto Case "No" .Cells(1).Shading.BackgroundPatternColorIndex = wdAuto .Font.ColorIndex = wdRed Case Else .Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight .Font.ColorIndex = wdAuto End Select End With ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd End Select Select Case .Title Case "GM" If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd With .Range Select Case .Text Case "Higher than ORA" .Cells(1).Shading.BackgroundPatternColorIndex = wdAuto .Font.ColorIndex = wdAuto Case "Lower than ORA" .Cells(1).Shading.BackgroundPatternColorIndex = wdAuto .Font.ColorIndex = wdRed Case Else .Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight .Font.ColorIndex = wdAuto End Select End With ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd End Select Select Case .Title Case "Cash" If ActiveDocument.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect Password:=StrPwd With .Range Select Case .Text Case "Positive" .Cells(1).Shading.BackgroundPatternColorIndex = wdAuto .Font.ColorIndex = wdAuto Case "Negative" .Cells(1).Shading.BackgroundPatternColorIndex = wdAuto .Font.ColorIndex = wdRed Case Else .Cells(1).Shading.BackgroundPatternColorIndex = wdNoHighlight .Font.ColorIndex = wdAuto End Select End With ActiveDocument.Protect wdAllowOnlyFormFields, True, StrPwd End Select End With End Sub |
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 |