Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word Tables

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 05-09-2018, 02:49 PM
AustralianKate AustralianKate is offline Windows 10 Office 2016
Novice
 
Join Date: May 2018
Location: Australia
Posts: 6
AustralianKate is on a distinguished road
Cool Assigning values to form fields and calculating results

Hi All,

Hoping you can help.

My question is similar to http://www.msofficeforums.com/word-t...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
File Type: doc Content Value.doc (43.0 KB, 2 views)

Last edited by macropod; 05-15-2018 at 11:10 PM. Reason: Discussion split to new thread
Reply With Quote
  #2  
Old 05-09-2018, 03:44 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

What Word version(s) are you using on the Macs? AFAIK, Word content controls work on recent versions of Mac Word.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 05-09-2018, 05:38 PM
AustralianKate AustralianKate is offline Windows 10 Office 2016
Novice
 
Join Date: May 2018
Location: Australia
Posts: 6
AustralianKate is on a distinguished road
Default

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).
Reply With Quote
  #4  
Old 05-09-2018, 05:41 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

But which 'older' versions? AFAIK content controls work on Mac Word 2013, too.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 05-09-2018, 06:24 PM
AustralianKate AustralianKate is offline Windows 10 Office 2016
Novice
 
Join Date: May 2018
Location: Australia
Posts: 6
AustralianKate is on a distinguished road
Default

Hi Macropod - Office for Mac 2011.
Reply With Quote
  #6  
Old 05-09-2018, 11:43 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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: http://www.msofficeforums.com/word-t...html#post76233. All the calculations in that document are done via field coding, without the need for macros.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 05-13-2018, 06:57 PM
AustralianKate AustralianKate is offline Windows 10 Office 2016
Novice
 
Join Date: May 2018
Location: Australia
Posts: 6
AustralianKate is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 05-13-2018, 09:32 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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 http://www.msofficeforums.com/word-t...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
[MS MVP - Word]
Reply With Quote
  #9  
Old 05-15-2018, 03:47 PM
AustralianKate AustralianKate is offline Windows 10 Office 2016
Novice
 
Join Date: May 2018
Location: Australia
Posts: 6
AustralianKate is on a distinguished road
Default

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
File Type: docx Content Value2.docx (16.4 KB, 2 views)
Reply With Quote
  #10  
Old 05-15-2018, 08:21 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
File Type: docx Content Values.docx (20.3 KB, 7 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #11  
Old 05-15-2018, 10:53 PM
AustralianKate AustralianKate is offline Windows 10 Office 2016
Novice
 
Join Date: May 2018
Location: Australia
Posts: 6
AustralianKate is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating values from Drop Down Selections bbucher Word 15 04-09-2017 08:53 PM
Expanding tables with calculating form fields? Father Word VBA 51 12-23-2016 07:52 PM
Assigning values to formfield checkboxes in a table mammiano Word Tables 13 01-12-2015 06:27 AM
Assigning Values to content control checkboxes and calculating results creative cathy Word Tables 13 10-07-2012 08:52 PM
Calculating Form Fields in Microsoft Word wubba80 Word 1 06-25-2010 12:42 AM


All times are GMT -7. The time now is 04:55 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft