?
Solved

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

Posted on 2011-09-02
7
Medium Priority
?
601 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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…
This collection of functions covers all the normal rounding methods of just about any numeric value.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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 …

771 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