Sorting a checkbox table

[Nosmo King]

I created a Word checkbox table that contains a list of possibilities. I would like to sort these possibilities according to whether or not the box is checked on exit from the table. For example, I have this table:

__Possibility1
X Possibility2
__Possibility3
X Possibility4

Possibilities 2 and 4 get checked. On leaving the table, then, I want the table replaced by a list that looks like this:

Group 1 contains possibilities 2 and 4.
Group 2 contains Possibilities 1 and 3.

I suspect this can be done, but I have no idea how to do it. Thanks for the newbie help.

[Nosmo King]

I figured out a work-around, but if anybody has a better way, I'd like to hear it.

Instead of a checkbox table, I created a simple two column table, and populated the first column with "$$$" whereever I wanted a check. I was then able to sort the table correctly, and convert to text. Find and replace gets rid of the "$$$", and allows correct syntax in my resultant sorted list.

I can post the code if anyone's interested, but it's pretty simple to do.

It's not as convenient to use "$$$" as it is a checkbox, since I have to do two actions (mouse click and macro to populate each row of column 1 with each "$$$") instead of one, but it works.

Thanks.

[macropod]

Hi Nosmo King,

If you used dropdown formfields (with checkbox characters for selection) instead of checkbox formfields, it would be possible to encode the output with formula fields instead of vba.

[Nosmo King]

I still would have to do two clicks for every box I want checked, wouldn't I? One on the dropdown field, and one on the checkbox character. So that doesn't solve the two click problem in my workaround.

I'm not sure what "encode the output with formula fields" means.

[macropod]

Granted, you'd need two clicks for every box you want checked, but you wouldn't need to deal with the "$$$" strings.

To see what I mean about using formula fields, check out the attachment. Note that it works without macros.

[Nosmo King]

That's pretty cool. I think I can make the drop down table, but how do you get the possibilities in the right order without macros?

Let me explain further what I need. I have a list of 50 yes/no questions on a form, and need to separate the yes answers from the no answers.

Thanks for the help.

[fumei]

macropod, as I hope you know, I stand in awe of your Field expertise.

But I have to ask, why do you consider "Note that it works without macros." so important?

I look at the intricate nested If statements within the field codes and it makes me want to run away screaming. It just seems so easy to make an error. I don't know, perhaps because it looks so complicated.

I understand there can be issues with VBA vis-a-vis Security, but to my mind, I would MUCH rather use VBA where I can use structure (and comments!), that (IMO) is far easier to read, and understand.

Nosmos King, you can certainly go macropod's routes as I am sure there is a way using fields will work for you. As a possible alternative take a look at the demo attached.

"I want the table replaced by a list that looks like this:

Group 1 contains possibilities 2 and 4.
Group 2 contains Possibilities 1 and 3."

The demo does not have a table. It has 14 (but it could have been 50) checkboxes.

The LAST checkbox has a OnExit procedure, so you must move into the last checkbox, and out. In other words, if you ignored it (because you want to have it blank), the procedure will not fire. You can still leave it blank, you just have to move through it using Tab or the arrow keys.

Or you can fire the procedure by clicking "MaybeYes MaybeNo" on the top toolbar.

The procedure builds two arrays, one for checkboxes that ARE checked, and one for checkboxes not checked.

It grabs the number by getting the number from the names of the checkboxes: "Check1"..."Check14"

It builds a result string.

It removes all the formfields. I did this because you wrote: "I want the table replaced by a list"

It inserts the result string.

Example:

Group 1 contains possibilities 2, 4, 7, 9, 11, 12, 13.
Group 2 contains possibilities 1, 3, 5, 6, 8, 10, 14.

Here is the code.
Option Explicit


Sub MaybeYesMaybeNo()
Dim DocFF As FormFields
Dim oFF As FormField
Dim Result As String
' the yes it is checked array
Dim strYes()
' the no it is NOT checked array
Dim strNo()
Dim j As Long
Dim k As Long
Dim var, var2

Const Grp1 As String = "Group 1 contains possibilities "
Const Grp2 As String = "Group 2 contains possibilities "

Set DocFF = ActiveDocument.FormFields
For Each oFF In DocFF
If oFF.Result = True Then
ReDim Preserve strYes(j)
strYes(j) = _
Replace(oFF.Name, "Check", "")
j = j + 1
Else
ReDim Preserve strNo(k)
strNo(k) = _
Replace(oFF.Name, "Check", "")
k = k + 1
End If
Next
' build the result string for the Yes - Group 1
' starting with opening text
Result = Grp1
' adding each Yes
For var = 0 To UBound(strYes())
Result = Result & strYes(var) & ", "
Next
' fix the last comma and space
Result = Left(Result, Len(Result) - 2)
' add the period at the end, plus a paragraph mark
' plus the opening text
Result = Result & "." & vbCrLf & Grp2
' append the result string for the No - Group 2
For var2 = 0 To UBound(strNo())
Result = Result & strNo(var2) & ", "
Next
' fix the last comma and space
Result = Left(Result, Len(Result) - 2)
' add the period at the end
Result = Result & "."


With ActiveDocument
' turn off protection
.Unprotect
' delete all the formfields via bookmark
.Bookmarks("FF").Range.Delete
' insert the Result string at Result bookmark
.Bookmarks("Result").Range.Text = Result
End With
End Sub

[macropod]

Hi Gerry,

There are two reasons for going down the field route:
1. local security profiles preventing macros from running; and
2. the risk that the user will disable macros upon oipening the document.

Yes, my field coding looks complicated compared to your vba approach, but it's really little more than the sort of in-line if tests one might use in vba. In any event, your vba code is simpler largely because your output:
1. is expressed always in the plural form;
2. says "Group # contains possibilities ." if there are no possibilities in a particular group; and
3. doesn't insert the 'and' between the last two of a set of multiple possibilities,
all of which are poor english.

That aside, I'd much rather use vba than field coding for anything more than perhaps 5 possibilities.

Nosmo King: For the field-based output, it really doesn't matter in what order the fields occur. What matters is the field bookmark names. For a vba-based approach, though, the order may be crucial, depending the code you use to process the collection.

Unless there's a particular need to do so, I'd recommend against deleting the source formfields (which field coding can't do anyway), because doing so gives the user no way of correcting an error. If you don't want the formfields to print, you could format them as hidden text, or put them on a 'front' page that can be discarded - or you could use a userform.

[Nosmo King]

Fumei,

Wow, this is exactly what I wanted. Thanks! This is much better than my work-around.

The form is only for my use, not another user, so there really isn't a problem with using macros or grammar problems. If there is nothing in one of the groups, I don't need the form sorted. Because the output I need is text, I don't want a table, and since I'm not saving the original data for any reason, I don't think I need to preserve the original form.

Thaks again for all the work.

Nosmo

[Nosmo King]

Fumei,

I've been playing with this for a while, now, and I'm still having some problems getting the result I need. I think I have the general idea (although I doubt I could reproduce it!)

Here's the problem. I really need a string result that doesn't contain numbers, but the name of the label to the right of the checkbox.

I tried just eliminating the "Check" in your strYes(j) and strNo(k) arrays, then renaming the checkbox according to the label name to the right. This seems to work as long as there is only one word in the name of the checkbox. If I want two words, I'm not sure how to do it.

For example:

_X_ Bob Smith
___ Sally Jones
_X_ George White

returns

"Group 1 contains Bob Smith and George White.
Group 2 contains Sally Jones."

Thanks again for the help.

[Nosmo King]

Well, I have a way that works, but it's pretty inelegant. If anybody has a better way of doing it, I'd like to hear it.

I added a series of if-then statements to Fumei's code like this:

For Each oFF In DocFF
If oFF.Result = True Then
ReDim Preserve strYes(j)
strYes(j) = _
Replace(oFF.Name, "Check", "")

'my addition starts here
If strYes(j) = "1" Then
strYes(j) = "Bob Smith"
End If
If strYes(j) = "2" Then
strYes(j) = "Sally Jones"
End If
If strYes(j) = "3" Then
strYes(j) = "George White"
End If
'my addition ends here

j = j + 1
Else
ReDim Preserve strNo(k)
strNo(k) = _
Replace(oFF.Name, "Check", "")

'my addition starts here
If strNo(k) = "1" Then
strNo(k) = "Bob Smith"
End If
If strNo(k) = "2" Then
strNo(k) = "Sally Jones"
End If
If strNo(k)) = "3" Then
strNo(k) = "George White"
End If
'my addition ends here

k = k + 1
End If



This seems to work, but there must be a better way.

Thanks.

[macropod]

Hi NosmoKing,

If you named your formfields with the peoples' names, with underscores separating each word in the names, eg:

Bob_Smith
Sally_Jones
George_White

you would then only need to change both occurrences of:
Replace(oFF.Name, "Check", "")
to:
Replace(oFF.Name, "_", " ")
in fumei's code to get the results you're after.

[Nosmo King]

Duh. I knew there must be a more elegant way to do it than the way I did! Will that eliminate both underscores if I have a three-worder (Billie_Sue_Brown)?

[macropod]

Yes.

[Nosmo King]

Thanks. Works like a charm, and doesn't look so kludgey (is that a word?) There does appear to ba a 20 character limit on bookmark names, though.

[Nosmo King]

I've been working with this code now for several weeks, and it's working well enough that I want to expand it further. I have another group of 25 yes/no questions that are similar to the first group that I've been working with. I'd like to be able to sort those checkboxes as well, but it needs to be done in separate sections.

The checkbox would look like this:

_x_ Bob Smith
___ Sally Jones
_x_ George White

(next section)
_x_ Bill Wilson
___ Will Baker
_x_ Bryan Stein


Output would then look like this:

Group 1 contains Bob Smith and George White
Group 2 contains Sally Jones

Group 3 contains Bill Wilson and Bryan Stein
Group 4 contains Will Baker

It seems to me that this could be done by using a for/next loop if I could somehow dimension the checkboxes in the first section as one array, and then dimension the second section as a second array.

The current array is dimensioned as "Dim DocFF As FormFields" then defined by "Set DocFF = ActiveDocument.FormFields". I presume that this sets all the form fields.

Is there a way to set just one section of the form fields to an array?

I'm not entirely sure that I explained this correctly, but if anybody understood what I mean, thanks for struggling through my attempt.

[macropod]

Hi NosmoKing,

There's potentially a fundamental problem with using the peoples' names for your expanded requirements: a given bookmark (which is what you're using) can only exist in one place in a document. Thus, if you name a formfield 'Bob_Smith' in the first Section, then name another 'Bob_Smith' in the second Section, the first bookmark will be deleted.

Subject to the above proviso, you could use code like the following re-worked version of fumei's code:
Sub MaybeYesMaybeNo()
Dim DocSec As Section, oFF As FormField, Result As String
' strYes = checked array, strNo = UNchecked array
Dim strYes(), strNo()
Dim j As Long, k As Long
Dim var, var2
Const Grp1 As String = "Group 1 contains possibilities "
Const Grp2 As String = "Group 2 contains possibilities "
' turn off protection
ActiveDocument.Unprotect
For Each DocSec In ActiveDocument.Sections
For Each oFF In DocSec.Range.FormFields
If oFF.Result = True Then
ReDim Preserve strYes(j)
strYes(j) = Replace(oFF.Name, "_", " ")
j = j + 1
Else
ReDim Preserve strNo(k)
strNo(k) = Replace(oFF.Name, "_", " ")
k = k + 1
End If
Next
' build the result string for the Yes - Group 1
' starting with opening text
Result = Grp1
' adding each Yes
For var = 0 To j - 1
Result = Result & strYes(var) & ", "
Next
' fix the last comma and space
Result = Left(Result, Len(Result) - 2)
' add the period at the end, plus a paragraph mark
' plus the opening text
Result = Result & "." & vbCrLf & Grp2
' append the result string for the No - Group 2
For var2 = 0 To k - 1
Result = Result & strNo(var2) & ", "
Next
' fix the last comma and space
Result = Left(Result, Len(Result) - 2)
' add the period at the end
Result = Result & "."
' delete all the formfields via bookmark
ActiveDocument.Bookmarks("FF" & DocSec.Index).Range.Delete
' insert the Result string at Result bookmark
ActiveDocument.Bookmarks("Result" & DocSec.Index).Range.Text = Result
Next
End SubNote that, for the above code to work, you'll also need to:
1. have a Section break between each group of fields;
2. bookmark each Section's group of formfields with 'FF' plus the Section number; and
3. have a bookmark for each Section, named 'Result' plus the Section number.

[Nosmo King]

Thanks for the prompt reply. I'm going to have to study your code a bit before I can ask intelligent (<g>) questions about it, but I can see several areas right away that confuse me.

What kind of section break do I insert? Continuous, next page, etc?

I'm not sure how to bookmark each section's group of formfields with 'FF' plus the Section number, or how to bookmark for each Section, named 'Result' plus the Section number. I guess this is the problem when you try to help a total noob!

I know how to insert a bookmark at the cursor position. To bookmark a section, do I highlight the entire section, then place the bookmark? Do I then do it twice, naming one "FF1" and the other "Result1" (for the first section), or do these two bookmarks go in different places?

The problem of Bob Smith in both sections could be a killer. I may be able to work around it (by having slightly different names) , but is there an easy way to fix it?

Thanks for your time.

[macropod]

Hi NosmoKing,

Any kind of Section break will do.

Your current document has a bookmark named 'FF'. If you press F5 (Goto), choose 'Bookmarks', then 'FF', Word will highlight the range to which that bookmark applies. Given that this is in Section 1 of your document, you'd remark that range with a bookmark named 'FF1'. Likewise, once you've created the formfields for the 2nd Section, you'd select them and apply a bookmark named 'FF2', and so on. Similarly, you'd assign a 'Result#' bookmark to a range outside the range covered by the 'FF#' bookmark in each Section.

[fumei]

1. "Is there a way to set just one section of the form fields to an array?"

"I'm not sure how to bookmark each section's group of formfields with 'FF' plus the Section number"

You do not need to bookmark the Section. You can refer to a Section's collection of formfields directly. You can make a collection of formfields for any specific Section (if this is what you want to do) by:
Dim Sec1_FF As FormFields
Dim Sec2_FF As FormFields
' note the plural

Set Sec1_FF = ActiveDocument.Sections(1).Range.FormFields
Set Sec2_FF = ActiveDocument.Sections(2).Range.FormFields


"The current array is dimensioned as "Dim DocFF As FormFields". Note, this is NOT an array, and therefore is not dimensioned. DocFF is an object. You Set objects. You do not Set arrays.

2. Naming is always an issue. There is no way around it. If applicable, you could possibly use S1Bob_Smith (for section 1), and S2Bob_Smith (for section 2). You will have to figure out how you want to name things. Just be consistent and clear.

3. Please remember that formfields HAVE bookmarks, but they are not themselves bookmarks. It is very possible to have a valid formfield (it takes input, contains a .Result, etc. etc.) that does NOT have a bookmark.

4. macropod's comment vis-a-vis user corrections (not possible if the formfield is deleted) is important, and should definitely be considered within your design. As you state that this is essentially for your own usage, this may not be vital, but something to think about.

5. Frankly...a userform would probably be the way I would go. IMO, there is a level of complication with formfields IN a document that, once reached, pushes me to build a dedicated interface (a userform) instead.

6. regarding Group versus Groups (singular vs plural), this could easily be done logically, by a counter. If counter = 1, use "Group", if not use "Groups".

Finally, if you are requiring to work with Sections (and this is not a bad idea at all), you can easily pass the Section as a parameter to your actioning procedure.

Sub MaybeYesMaybeNo(Section_In As Section)
Dim oFF As FormField
Dim Result As String

For Each oFF In Section_In.Range.Formfields

...yadda yadda yadda



OR.........
Sub MaybeYesMaybeNo(Section_In As Section)
Dim Section_FF As FormFields ' note plural
Dim oFF As FormField
Dim Result As String

Set Section_FF = Section_In.Range.FormFields

...yadda yadda yadda



Now you can Call the procedure to action any Section you like.

Call MaybeYesMaybeNo(ActiveDocument.Section(x))

[macropod]

Hi fumei,

I had considered simply deleting the formfields in each Section as you suggest but decided to stick with your original approach as using a bookmarked range allows for the possibilty that there's some descriptive text alongside each formfield and that that text will also need to be deleted.

[Nosmo King]

OK, here goes.

Fumei, your code is a bit over my head. As is obvious from my questions (confusing arrays with objects, etc.) I'm not even a beginner! I wasn't able to understand how to use the code you so graciously provided. My apologies, and I appreciate the effort.

Macropod, I created a test doc that contained a copy of your code, and two sections of checkboxes that were separated from each other by a page break. I bookmarked the first checkbox range FF1 and the second FF2. I then created a bookmark entitled Range 1 just below range FF1, while still in the first section. I created a bookmark entitled Range2 just below my range FF2 in section 2. I checked several boxes in each group, and ran the code.

The first section did just exactly what it was supposed to do, but then it crashed (without an error message.) Nothing happened to section 2.

What did I do wrong?

Thanks again.

[macropod]

Hi Nosmo King,

A page break is not a Section break ... See Insert|Breaks.

[Nosmo King]

Duh again! I was sure I hit section break(next page), but I went back and redid it, and both sections worked. I still got an error without an error message at the end, and I'm not sure what to do about that. The formatting is off, but I think I can fix that.

Thanks again.

[macropod]

Hi Nosmo King,

I still got an error without an error message at the end
What did the error message say? Was a particular line of the code highlighted?

[Nosmo King]

Whoops, I spoke too soon. I checked again, and there is a true section break at the end of section 1 (but not section 2). Now when I run it, Section 1 works OK, but section 2 disappears completely. I'm still getting an error (with no message) at the end, as well. I still have a result1 bookmark, but the result2 bookmark is gone. FF1 and FF2 are also MIA.

The error box says "error" and has an OK button. There's no other message, the VBA editor doesn't open, and there is no highlighted code.

Sorry to be having such problems.

[macropod]

Hi Nosmo King,

At a guess, I'd say you've placed the Result2 bookmark inside the FF2 bookmarked range.

[Nosmo King]

Argh. You're right.

Thanks.

[Nosmo King]

Well, it seems to be working, but the result strings aren't coming out correctly. Section 1 strings are correct. Section 2 seems to contain the results of Section1 and Section 2 in each group, as if the Result string doesn't get reset.

I added a third section, and Section 3 contains the results of all 3 sections, like this:


Section 1: Group 1 contains possibilities Bob Smith
Group 2 contains possibilities George White

Section 2: Group 1 contains possibilities Bob Smith, Sally Jones
Group 2 contains possibilities George White, Bryan Stein

Section 3: Group 1 contains possibilities Bob Smith, Sally Jones, Bill Wilson
Group 2 contains possibilities George White, Bryan Stein, Will Baker

Bob Smith and George White aren't in sections 2 or 3.

Any ideas?

[Nosmo King]

I think I've figured this one out. If I add

j=0
k=0

after "For Each DocSec In ActiveDocument.Sections"

it seems to work.

[Nosmo King]

One more question. I'm not sure how to delete all the section breaks in the document. Is there an easy way, or do I have to do it manually?

[macropod]

Hi Nosmo King,

try:
Dim oSec As Section
For Each oSec In ActiveDocument.Sections
oSec.Range.Characters.Last.Delete
Next oSec

[Nosmo King]

You know, you guys are great! I could never have attempted this project without your help.

Thanks.

[fumei]

Word of caution.

While macropod's code will indeed delete the section breaks, IF you have specific headers or footers for those Sections, you may get some problems.

Of course if you do not have headers and footers then this is not any problem.

I have to wonder why you want to delete the Sections though.