Solved

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

Posted on 2011-09-02
7
599 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
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…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
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…

737 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