Solved

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

Posted on 2011-09-02
7
598 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
ID: 36476343
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
ID: 36476400
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
ID: 36476445
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 36476468
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
ID: 36478561
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
ID: 36478609
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
ID: 36478887
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

828 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