Hi All,



Hoping you can help.

My question is similar to https://www.msofficeforums.com/word-...g-results.html in that there are three sets of check boxes in the form and I want to substitute values for each of these, so that when someone selects the check boxes, values are returned and then summed to come up with a total score. That total score then translates back to an overall ranking (e.g. 3-6=Low, 7-10=Medium, 11-15=High or 17-21=Critical).

However, I have a Word dotm form that is set up with form fields (legacy) because I have Mac users as well as PC users (and I understand Active X and Content Controls don't work for Macs).

I assume I cannot use the terrific VBA code that Macropod posted in that thread because it relates to Content Controls... or are there modifications that can be made to that code so that I could use it in my document?

I have attached an excerpt from dotm file (doc) containing the content in question.

Thanks so much for your help.

Kate.

Attached Files

Content Value.doc (43.0 KB, 12 views)

What Word version(s) are you using on the Macs? AFAIK, Word content controls work on recent versions of Mac Word.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Macropod. Thanks for getting back to me so quickly! Some users are on Office 2016, but others are on older versions (I just asked IT).

But which 'older' versions? AFAIK content controls work on Mac Word 2013, too.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Macropod - Office for Mac 2011.

OK, in that case you'll need formfields. Is there a reason you can't use formfield dropdowns instead of checkboxes for the selections? That would make it far easier to do the setup. See, for example: https://www.msofficeforums.com/word-...html#post76233. All the calculations in that document are done via field coding, without the need for macros.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Macropod. Your example is a very clever way using field codes to manage drop-down values. I would prefer to (/chose to) use check boxes for two reasons: 1. so that I could add explanatory text (in status bar) for each of the items (they're not that self-explanatory and a drop-down list didn't give me enough space for the explanatory text I wanted to use). 2. It is quicker and easier for users to see what options are available and to choose the appropriate one... but your solution using field codes is still a pretty good solution.

There is no reason you can't add explanatory text to the status bar when using dropdowns. In any event, given the amount of unused space that would become available by using dropdowns, that space, too, could be used for explanatory text.

The problems with using formfield checkboxes include:
1. They cannot be used without macros for any calculations; and
2. Require additional code to ensure only one option is checked.

If you're wedded to the checkbox route, you could adapt the code I wrote for the content control version of the code in https://www.msofficeforums.com/word-...g-results.html to work with formfields instead. A fair bit of re-working would be required, though (e.g. there is no generic FormFieldOnExit event, so you'd need to add an on-exit macro call to every checkbox in the form).

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Macropod. Thanks for this.

In the end, I went with your suggestion and used drop-down boxes with a description of each of the selections in text below the box... and it works very well.

I am managing to calculate the values pretty easily but can't manage to translate that back to a total ranking. (Ranking column.)

i.e. If Total (bookmark) is less than 3, then display "NA". If Total is less than 7 then display "Low". If Total is less than 11 then display "High" and if Total is less than 21 then display "Critical". Alternatively, if H5 (cell reference) is less than 3... etc., as above.

I've tried a number of things, but I just don't seem to be getting the syntax right. I have tried:

{IF “{REF Total}”<3 “NA}{IF “{REF Total}”<7 “Low”} … etc.
{IF H5<3 “NA”}{IF H5<7 “Low”} … etc. (referencing cell number)
{IF Total<3 NA} {IF Total<7 “Low”} … etc.

Would you please help?

I've attached the section in question.

Thanks again,

Kate.

Attached Files

Content Value2.docx (16.4 KB, 11 views)

See attached. I've simplified the individual totals, e.g.:
{IF{REF Impact}= "Critical" 7 {IF{REF Impact}= "High" 5 {IF{REF Impact}= "Med" 3 {IF{REF Impact}= "Low" 1 0}}}}
and created the overall tally:
{IF{=E8}< 3 "NA" {IF{=E8}< 7 "Low" {IF{=E8}< 11 "High" "Critical"}}}

Attached Files

Content Values.docx (20.3 KB, 19 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

It works!!!

Thank you so much Macropod! You've made my day.

Also, thank you for tidying up my other 'code' and showing me a better way to manage if statements. Really appreciated.

Have a fabulous day/evening/morning, wherever you are!!

Kate.