asked on

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

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?

GrahamSkan

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

300143

ASKER

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

GrahamSkan

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

GrahamSkan

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

300143

ASKER

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

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

300143

ASKER

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