Add / Sum checked Checkboxes labelled 1, 2, 3, etc. in Word Table - Need Macro?
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 1s:
=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 9s:
=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 (dont 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!!!
|