Add / Sum checked Checkboxes labelled 1, 2, 3, etc. in Word Table

klearazkrystal · #1 03-30-2019, 08:58 PM

Add / Sum checked Checkboxes labelled 1, 2, 3, etc. in Word Table - Need Macro?

I have a table in Word that has checkboxes. In the "9-Cell" version, the number 1 appears 8 times, 2 appears 8 times, all the way through to 9 appearing 8 times. The idea is to choose whether 1 or 2 is preferable, 2 or 3, ..., 8 or 9; then next column 1 or 3, 2 or 4, ..., 7 or 9; etc. So in the end it compares all combinations. There are 36 options in total.

I need to be able to work out how many labelled "1" are checked, how many labelled “2” are checked, etc. Then in my output table, I need to sort by this result, in descending order (so the option chosen the most is at the top).

I have managed to get this to work fine using drop-downs with values, then giving each a bookmark, then doing an IF statement for each, referring to the bookmark and values. I then updated the fields and sorted the table.
[in case this helps anyone else searching, this is what I did for all the “1”s:
=IF(Choice01=1,1,0)+IF(Choice02=1,1,0)+IF(Choice03 =1,1,0)+IF(Choice04=1,1,0)+IF(Choice05=1,1,0)+IF(C hoice06=1,1,0)+IF(Choice07=1,1,0)+IF(Choice08=1,1, 0)
Another calculation for all the “9”s:
=IF(Choice08=9,1,0)+IF(Choice15=9,1,0)+IF(Choice21 =9,1,0)+IF(Choice26=9,1,0)+IF(Choice30=9,1,0)+IF(C hoice33=9,1,0)+IF(Choice35=9,1,0)+IF(Choice36=9,1, 0)
Where ChoiceNN is the bookmark for the correct drop-down out of the 36.
It was fiddly, but it did the job]

HOWEVER, apparently it does need to be checkboxes, so it is less clicks for the user. With the drop-downs it ends up 3 clicks, one to select the drop-down, one to press the arrow, then one to choose the desired number. Whereas with 2 checkboxes showing in the cell, it's only 1 click.

From what I have researched, I need a macro to calculate this. I could write this in pseudo code, as I understand the general logic required as such, but I have no idea how to write this as a proper macro, and how to tell whether or not the checkbox has been checked. To be honest, I've never written a macro, and I don't even know how to insert it into my document (so I may need some pointers on that too)! :-/

I just inserted the checkboxes using the option in the attached image (don’t know which sort they are). I didn't set them up in any special way (other than changing what the checked mark looks like). I don't know how to assign a value to this sort of checkbox (with the older ones you could give them a label / bookmark within the screen, but I don't think I can use that type, and then change them to be the filled square instead of cross, which is ideal. If anyone can get it to work with a different sort of checkbox instead, we may be able to get away with the cross.

I have attached a dummy version of the table in Word, showing it blank, as well as completed how it should appear in the end. I have also attached some screenshots.

Note there is an 18-Cell and 19-Cell version too... So in the end I need to be able to amend the code to work for those as well.

If anyone can please help, that would be so greatly appreciated!!!

Fingers crossed this is possible, otherwise I'm also up for any other suggestions of how to achieve this. I have also (preferably in addition to, not as a replacement) thought about finding an online solution, but it would need to be something free (ideally) that I could set this up in, have it collate the data, then present results, and email back. It would need to be relatively secure. With ability to format the look and feel of the form. Any ideas?

Thanks so much for your help!!!

gmayor · #2 03-30-2019, 10:48 PM

It would need some changes - most important of which would be to title the check box content controls and add content controls for the calculations and total. It should also be a template as it will need macros. - see attached (removed and updated in later reply).

klearazkrystal · #3 03-30-2019, 11:51 PM

Hi, thanks so much for this!

The first time I tried to run it, I could check the boxes, but the Calculate Totals button didn't work.

I looked at the code, and it's pretty, but I have no idea how to edit anythng.

The second time I opened the doc (just a copy each time), I can't seem to check the boxes...?

Also, will it be hard to copy and paste the macros into my proper template (I now know it needs to be .dotm)? I can see how you've labelled the boxes & fields and I should be able to copy those back in, and just copy the button (I have no idea where you have even got that from).

I fear this all may be a little above my capability :-/ (I am much more of a document formatter, working with proper Styles and layout)

gmayor · #4 03-31-2019, 02:57 AM

Create new documents from the template. (File > New) These will be cleared of selections. Select as many or as few check boxes as you wish then click the calculate button. You can make changes and recalculate in that document.

I have modified the attachment slightly. Just re-download and copy the ModMain module to your template (drag and drop with both open) and copy the contents of the ThisDocument module to the matching module in your template.

The Button is an ActiveX button from the Developer Tab, Controls Group and it simply runs the CalculateTotals macro which counts up the various check box results and writes them to the appropriate results content controls and then totals the selections and sorts the results.

It all works on the basis that your check box content controls all have the same titles - in this Case 1,2,3,4,5,6,7,8,9 as appropriate and that the results have corresponding titles Calc1 to 9. There is also a Total content control.

You may find http://www.gmayor.com/insert_content_control_addin.htm useful to edit the controls and add titles as appropriate in your template.

klearazkrystal · #5 03-31-2019, 07:51 PM

[Part 1]

Thanks so much!

It's still not running for me however :-(

A copy of the template gave this security warning, attached, which I enabled.

But I can't click any checkboxes and the button doesn't do anything.

klearazkrystal · #6 03-31-2019, 07:55 PM

[Part 2]

These are my Macro settings. Is this why?

Is there a way around this? As the document could be emailed to various companies, and we can't ask them to change their macro settings.

Thanks again!

gmayor · #7 03-31-2019, 09:23 PM

The template should go in a trusted folder, and must not be blocked by your antivirus software; however if you are going to distribute this document, you should distribute it as a document and without the macros as you will not be able to force people to use macros. Better that you should batch process the returned documents.

klearazkrystal · #8 03-31-2019, 11:50 PM

I will pass on this feedback. I really appreciate your help, thanks so much.

Hopefully this assists someone else in the future too.

Cheers.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Need Macro to select Checkbox after two boxes are checked in a series of checkboxes	rsaini	Word VBA	0	01-16-2018 11:57 AM
Assigning values to formfield checkboxes in a table	mammiano	Word Tables	13	01-12-2015 06:27 AM
Pivot Table Question- Can the table display in the order the boxes were checked/selected?	blackjack	Excel	0	09-13-2014 08:57 PM
Checkboxes are unchecked in Listbox that were checked off before.How can I stop this?	Rochelle711	Excel Programming	0	06-22-2014 06:16 PM