Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    Ashland, Oregon, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto filling of form cells (Office/Word 2000)

    I want to semi-automate the filling in of data on a form done in Word. Currently, each user has to key in all data, which is prone to mistakes. Setting up a pull-down list box is the way I'm choosing to pare down the mistakes. Next I'd like to link what gets selected and put into one cell so that another, related/linked cell gets automatically filled in. I've included an attachment that I've been using to test my solutions. What I think needs to be done is to actually create a 2 column pull-down list where the 2nd column is hidden. When a user selects one of the values in the visible column the the corresponding value in the hidden column should get auto-pasted into a related/linked cell further down on the page (see the attached example).

    Any help with this would be appreciated. I'd like to keep this as simple as possible as there are 20 - 40 linked cells on about 15 different template forms that we use. Thanks again for the help...
    Bill
    Attached Files Attached Files

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Auto filling of form cells (Office/Word 2000)

    I don't recall Word's form drop-downs supporting two columns. Instead, you could embed a "key" at the end of the item, for example:

    Acura NSX, Red, 2002 {A21}

    In your exit macro, you would read out the key and use that to look up the correct information for your second field. You have numerous choices on how to do this, including coded into your macro, stored in the document in document variables, stored on disk somewhere (text file or database file), etc. Does this help?

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto filling of form cells (Office/Word 2000)

    Hi Bill:
    I looked at your attachment. I must be missing something. You've already linked the dropdown to the final cell. Why do you need the intermediate step of placing the result in a hidden cell to be read by the final cell?

  4. #4
    New Lounger
    Join Date
    Jun 2003
    Location
    Ashland, Oregon, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto filling of form cells (Office/Word 2000)

    I know how to make the link as demo'ed by the attached example. What I don't know how to do is to do a data conversion. I did put some notes/examples at the bottom of how I want the conversions to be made but obviously these aren't very clear . So let me try again:

    The attached file just puts the very same value from the first table into the corresponding cell of the 2nd table. The notes at the bottom of the attachment were meant to describe an array of corresponding data strings. For instance, if a user types in 2+ in the top cell, I want the corresponding cell in the 2nd table to read "mild," not "2+" like it currently does; If they type in "4+" I want the corresponding cell in the 2nd table to read "severe:" etc.

    Here's the matrix/array info again:
    Rating Descriptive phrase
    **********************************
    0 = 0
    trace = trace
    1+ = mild
    2+ = mild
    3+ = moderate
    4+ = severe
    2-3+ = mild to moderate
    3-4+ = moderate to severe
    trace-1+ = trace to mild

    What this is about is converting number ratings to a more descriptive term.

    Thanks for the help...

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto filling of form cells (Office/Word 2000)

    <P ID="edit" class=small>(Edited by Phil Rabichow on 09-Jul-03 19:35. see the Addendum)</P>Hi Bill:
    If I understand you correctly, what you need are IF fields. I've attached a small sample. Essentially, you have a dropdown field called Dropdown1 with your choices. I threw in a REF field to repeat the value of the dropdown, but it's not needed. Next you'll see IF fields to convert the 1+, 2+. etc. to a name. Essentially, you have

    {IF {REF bookmarkname } = {Quote "1+"} "mild" "We test for other possibilities" }

    I'm not sure that you need the quote field, but I wanted to make sure that 1+ was treated as text & not part of an equation. One problem. You need to update the field before you will see the result. You can do this in several ways.

    1. You can have a macro that unprotects, updates fields, & then reprotects without resetting the values. You can attach that as an On Exit macro to each formfield.
    2. You can either manually or use an On Exit macro to switch to Print Preview & back. That will update fields, but causes a little flicker.

    Perhaps one of the VBA experts will have a better solution.

    Addendum:
    I just realized after I posted this that the fields will update if you check off the box in the dropdown properties that says "Calculate on exit". It didn't work on my example because I only had one dropdown & everything else protected, so I never exited the dropdown. As long as you can tab out of the dropdown, Calculate on exit will update the fields.

    Hope this helps,
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Jun 2003
    Location
    Ashland, Oregon, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto filling of form cells (Office/Word 2000)

    Thanks Phil - slow me took a while but I finally figured out how to see the entire code for the 2nd field. Any idea how many conditions you can nest into a IF field? For the original example I sent I had 9 choices which is 8 nested if statements. When I try to revise your code to extend it out it doesn't work. I don't know enough to figure out if it's my syntax or the limitations of the IF field.

    TA ,

    Bill

  7. #7
    New Lounger
    Join Date
    Jun 2003
    Location
    Ashland, Oregon, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto filling of form cells (Office/Word 2000)

    Phil,

    I was able to get the 8 nested IF statements to work. Here's what the code looks like from a text editor:

    { IF { REF Dropdown1 }= "0" "0" "{ IF { REF Dropdown1 }= "trace" "trace" "{ IF { REF Dropdown1 }= "trace-1+" "trace to mild" "{ IF { REF Dropdown1 }= "1+" "mild" "{ IF { REF Dropdown1 }= "2+" "mild" "{ IF { REF Dropdown1 }= "2-3+" "mild to moderate" "{ IF { REF Dropdown1 }= "3+" "moderate" "{ IF { REF Dropdown1 }= "3-4+" "moderate to severe" "severe" }" }" }" }" }" }" }" }

    However, to get this to work, I could't just code it in a text editor and paste it in -- I had to work with your original code, alter it, then copy parts of it and paste it in as the next nested level then alter it for the next value, etc.

    Is there some kind of IDE for developing field codes other that the very limited dialog that pops up when you Insert > Fields > Document Automation > IF >Options?? The only object I could find in the dialog was a book mark name, no help with functions, syntax, etc.

    Anyhow, thanks for your help with this Phil -- you got me started done a track that works.

    Bill
    Attached Files Attached Files

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto filling form fields - IF fields (Word 2K)

    Hi Bill:
    The curly brackets that surround a field are actually field braces & can't be pasted from a text editor or typed; they are inserted by pressing Ctrl+F9. To get a field in Word, you have 3 choices only:
    1. Press Ctrl+F9 & type the syntax inside the field braces.
    2. Copy & Paste an actual field into a document or template.
    3. Insert/Field... from the menu, & make your choices inside the dialog box.

    The IF field essentially has the following syntax:
    { IF Condition "Then this result" "Else this result" }
    The condition consists of an object (like a bookmark, another field, etc.), an operator, & a value. The operators are =, >, <, <> (for not equal). Sometimes you put the value in quotes; sometimes you put the whole object-operator-value in quotes. I don't recall the limit to nested fields, but it is rather high. You can find it here. The way I modify IF fields is like this:

    Start with { IF "" "" ""} These quotes set the condition & the two results apart. Then type or paste the condition between the first set of quotes, the first result between the second set, & then, inside the last set of quotes, you put in another IF field, using Ctrl+F9. Then repeat the procedure by placing the double or triple set of quotes before you type the syntax. If I don't do that, I get lost. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    See <post#=273139>post 273139</post#> for an example document & an explanation of using IF fields to insert text based on form field results in a protected document.

    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •