Microsoft Office Forums Change color according with dropdown selection

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #16  
Old 08-24-2016, 03:30 PM
macropod's Avatar
macropod macropod is offline Change color according with dropdown selection Windows 7 64bit Change color according with dropdown selection Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,577
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default


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
[MS MVP - Word]
Reply With Quote
  #17  
Old 08-25-2016, 05:02 AM
Guessed's Avatar
Guessed Guessed is offline Change color according with dropdown selection Windows 10 Change color according with dropdown selection Office 2013
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,283
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

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
Reply With Quote
  #18  
Old 08-28-2016, 10:17 AM
palmiema palmiema is offline Change color according with dropdown selection Windows 7 64bit Change color according with dropdown selection Office 2010 64bit
Novice
 
Join Date: Aug 2016
Posts: 10
palmiema is on a distinguished road
Default

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?
Attached Files
File Type: docm Example.docm (128.0 KB, 18 views)
Reply With Quote
  #19  
Old 08-28-2016, 10:25 AM
palmiema palmiema is offline Change color according with dropdown selection Windows 7 64bit Change color according with dropdown selection Office 2010 64bit
Novice
 
Join Date: Aug 2016
Posts: 10
palmiema is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
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
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.
Reply With Quote
  #20  
Old 08-28-2016, 03:09 PM
Guessed's Avatar
Guessed Guessed is offline Change color according with dropdown selection Windows 10 Change color according with dropdown selection Office 2013
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,283
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

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
Reply With Quote
  #21  
Old 08-28-2016, 03:31 PM
macropod's Avatar
macropod macropod is offline Change color according with dropdown selection Windows 7 64bit Change color according with dropdown selection Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,577
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
Regarding your file type, do be aware that docx files cannot contain macros. You will need to use either: a docx file attached to a template containing the macro; or a docm file.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #22  
Old 08-28-2016, 04:05 PM
palmiema palmiema is offline Change color according with dropdown selection Windows 7 64bit Change color according with dropdown selection Office 2010 64bit
Novice
 
Join Date: Aug 2016
Posts: 10
palmiema is on a distinguished road
Default

Quote:
Originally Posted by Guessed View Post
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?
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!
Reply With Quote
  #23  
Old 08-28-2016, 04:16 PM
palmiema palmiema is offline Change color according with dropdown selection Windows 7 64bit Change color according with dropdown selection Office 2010 64bit
Novice
 
Join Date: Aug 2016
Posts: 10
palmiema is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
To colour the text & background so they're the same, you could use:
...
Regarding your file type, do be aware that docx files cannot contain macros. You will need to use either: a docx file attached to a template containing the macro; or a docm file.
Perfect! Thanks a lot for your help, macropod
Reply With Quote
  #24  
Old 08-31-2016, 12:02 PM
palmiema palmiema is offline Change color according with dropdown selection Windows 7 64bit Change color according with dropdown selection Office 2010 64bit
Novice
 
Join Date: Aug 2016
Posts: 10
palmiema is on a distinguished road
Default

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
Attached Files
File Type: docm Example.docm (126.4 KB, 14 views)
Reply With Quote
  #25  
Old 09-01-2016, 02:57 AM
macropod's Avatar
macropod macropod is offline Change color according with dropdown selection Windows 7 64bit Change color according with dropdown selection Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,577
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #26  
Old 03-13-2018, 02:09 PM
anakuprava anakuprava is offline Change color according with dropdown selection Windows 10 Change color according with dropdown selection Office 2016
Novice
 
Join Date: Mar 2018
Posts: 4
anakuprava is on a distinguished road
Default

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
Thank you
Attached Files
File Type: docm 1st page v.2.docm (23.3 KB, 8 views)
Reply With Quote
  #27  
Old 03-13-2018, 02:24 PM
macropod's Avatar
macropod macropod is offline Change color according with dropdown selection Windows 7 64bit Change color according with dropdown selection Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,577
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #28  
Old 03-13-2018, 03:01 PM
anakuprava anakuprava is offline Change color according with dropdown selection Windows 10 Change color according with dropdown selection Office 2016
Novice
 
Join Date: Mar 2018
Posts: 4
anakuprava is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
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.
Thank you, I changed it but it still does not seem to be working..
Reply With Quote
  #29  
Old 03-13-2018, 03:10 PM
gmaxey gmaxey is offline Change color according with dropdown selection Windows 7 32bit Change color according with dropdown selection Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 962
gmaxey will become famous soon enoughgmaxey will become famous soon enough
Default

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.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #30  
Old 03-14-2018, 11:04 AM
anakuprava anakuprava is offline Change color according with dropdown selection Windows 10 Change color according with dropdown selection Office 2016
Novice
 
Join Date: Mar 2018
Posts: 4
anakuprava is on a distinguished road
Default

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
Reply With Quote
Reply

Tags
color, dropdown, reaction

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
block selection in dropdown list Intruder Excel 2 01-10-2013 10:20 AM
Change color according with dropdown selection Dropdown selection value coconutt Word VBA 5 09-13-2012 05:23 PM
Change color according with dropdown selection Change cell color when selection is made from a drop down list fedcco Excel 12 08-28-2012 10:43 PM
Change color according with dropdown selection 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


All times are GMT -7. The time now is 09:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft