Solved

Is it possible to shade a cell in a word form / table based on the input?

Posted on 2011-09-02
7
595 Views
Last Modified: 2012-05-12
I have a word table saved as a form with input field.
One column of this form has a drop down choice of items:
Green
Yellow
Amber
Red
N/A
-
The question is is it possible to shade the cell that the field is if one of the four colours is chosen from the drop down box?
 e.g if Green is selected then the cell in question will be shaded green when tabbing to the next field

Any suggestions?
0
Comment
Question by:300143
  • 4
  • 3
7 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
You would need to use VBA to do it. This macro works for the first column of the first table.


Sub ColourCells()
    Dim tbl As Table
    Dim rw As row
    For Each rw In ActiveDocument.Tables(1).Rows
        Select Case GetCellText(rw.Cells(1))
            Case "Green"
                rw.Cells(1).Shading.BackgroundPatternColor = wdColorGreen
            Case "Yellow"
                rw.Cells(1).Shading.BackgroundPatternColor = wdColorYellow
            Case "Amber"
                rw.Cells(1).Shading.BackgroundPatternColor = wdColorDarkYellow
            Case "Red"
                rw.Cells(1).Shading.BackgroundPatternColor = wdColorRed
    End Select
    Next rw
End Sub

Function GetCellText(cl As Cell) As String
    Dim rng As Range
    
    Set rng = cl.Range
    rng.MoveEnd wdCharacter, -1 'drop cell formatting char
    GetCellText = rng.Text
End Function

Open in new window

0
 

Author Comment

by:300143
Comment Utility
Hi Graham
Thanks for prompt response.
Is it possible to get the above to work with a protected form?
Above works fine when the form is not protected but comes up with Microsoft Visual Basic message:
Run-time error '4605':
This command is not available
when the form is protected
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
You would have to remove and reset the forms protection
Sub ColourCells()
    Dim tbl As Table
    Dim rw As row
    
    If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
        ActiveDocument.Unprotect 'password
    End If
    For Each rw In ActiveDocument.Tables(1).Rows
        Select Case GetCellText(rw.Cells(1))
            Case "Green"
                rw.Cells(1).Shading.BackgroundPatternColor = wdColorGreen
            Case "Yellow"
                rw.Cells(1).Shading.BackgroundPatternColor = wdColorYellow
            Case "Amber"
                rw.Cells(1).Shading.BackgroundPatternColor = wdColorDarkYellow
            Case "Red"
                rw.Cells(1).Shading.BackgroundPatternColor = wdColorRed
        End Select
    Next rw
    ActiveDocument.Protect wdAllowOnlyFormFields, True ', password
End Sub

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
Try this for your second question. The macro has to be set to be the Exit macro for each relevant field in the table.
Sub ColourMyCell()
    Dim cl As Cell
    Dim v As Integer
    
    If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
        ActiveDocument.Unprotect 'password
    End If
    
    Set cl = Selection.Cells(1)
    v = ActiveDocument.FormFields("Dropdown1").DropDown.Value
    Select Case ActiveDocument.FormFields("Dropdown1").DropDown.ListEntries(v).Name
        Case "Green"
            cl.Shading.BackgroundPatternColor = wdColorGreen
        Case "Yellow"
            cl.Shading.BackgroundPatternColor = wdColorYellow
        Case "Amber"
            cl.Shading.BackgroundPatternColor = wdColorDarkYellow
        Case "Red"
            cl.Shading.BackgroundPatternColor = wdColorRed
    End Select
    
    ActiveDocument.Protect wdAllowOnlyFormFields, True ', password
End Sub

Open in new window

0
 

Author Comment

by:300143
Comment Utility
Hi Graham
Thanks for your response, I am attaching a word file (Test_01.doc) with this comment. The cells in the column Status retain the same colour of shading depending on what colour was chosen for the first cell in Status.
E.G. if yellow is chosen, the subsequent cell will be shaded yellow irrespective of what colour was chosen.
The table is protected, however there is no password protection set;
Am  I doing something wrong? Test-01.doc
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
Comment Utility
Sorry. Your situation wasn't entirely unambiguous. I presumed that you had a central dropdown form field whose selction you wanted to use when leaving any form field.

Actually you seem to need the colour to be determined by the selection in the dropdown in the particular cell that contains the dropdown field. Try this:
Sub ColourMyCell()
    Dim cl As Cell
    Dim v As Integer
    Dim ffld as formfield
    
    If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
        ActiveDocument.Unprotect 'password
    End If
    
    Set cl = Selection.Cells(1)
    Set ffld = cl.Range.FormFields(1)
    v = ffld.DropDown.Value
    Select Case ffld.DropDown.ListEntries(v).Name
        Case "Green"
            cl.Shading.BackgroundPatternColor = wdColorGreen
        Case "Yellow"
            cl.Shading.BackgroundPatternColor = wdColorYellow
        Case "Amber"
            cl.Shading.BackgroundPatternColor = wdColorDarkYellow
        Case "Red"
            cl.Shading.BackgroundPatternColor = wdColorRed
    End Select
    
    ActiveDocument.Protect wdAllowOnlyFormFields, True ', password
End Sub

Open in new window

0
 

Author Comment

by:300143
Comment Utility
Hi Graham
Works fine, just what I needed.
Thank you very much for your help.
Sorry I was not clearer in my original query.
Regards
300143
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now