Word: multiple checkbox answers

[WallIT]

Hi,

I have a simple userform with 4 checkboxes. In my example the values of these are set to colors:

checkbox1.value = "blue"
checkbox2.value = "red"
checkbox3.value = "green"
checkbox4.value = "yellow"

The user has to select at least one checkbox, but can select more than one, up to all 4 checkboxes.

If the user checks a single box, such as checkbox1, then then my code updates a bookmark to the value, in this case 'blue'.

However, if someone checks all the checkboxes the answer has to put commas between the values and the word "and" before the last value, so the bookmark then becomes...

'blue, red, green and yellow'

Basically, in simply language, I need to program...

If single checkbox selected then bookmark = checkboxX.value

If 2 checkboxes selected then bookmark = checkboxX.value + " and " + checkboxY.value

If 3 checkboxes selected then bookmark = checkboxX.value + ", " + checkboxY.value + " and " + checkboxZ.value

If 4 checkboxes selected then bookmark = checkboxX.value + ", " + checkboxY.value + ", " + checkboxZ.value + " and " + checkboxW.value

hope this makes sense?

I don't know how to do this grammatically. Can anyone point me in the right direction or reference a technique?

Thanks very much.

[fumei]

Does it matter what order? If it does not, then you pretty much have it.

Make a SINGLE string variable.
Test each checkbox if true.
If true, append the appropriate value to the string.
When all checkboxes evaluated, put the string into the bookmark.
Dim TheString As String
If Checkbox1.Value = True Then
TheString = TheString & "blue, "
End If
If CheckBox2.Value = True Then
TheString = TheString & "red, "
End If
etc.If checkbox1 if false, TheString remains ""; if true it is "blue, "
If checkbox2 is false, and checkbox1 was false, TheString = ""
If checkbox2 is true, and checkbox1 was false, TheString = "red"
If checkbox2 is true, and checkbox1 was true, TheString = "blue, red, "

I am not sure how you are getting "blue", "red" etc from checkboxes...

This is significant if you want those commas between determined values.

[WallIT]

Hi fumei,

Thanks for the quick reply. I think I understand your response, but it implies I would need 16 IF statements for the various combinations of checked checkboxes, correct?

[gmaxey]

No, you don't need 16 if statements. This is probably more complicated than it needs to be, but it will work with 4 or 400 similar checkboxes. The colore values are assigned to the checkbox "Tag" property and the checkboxes have the default enumerated titles:

Private Sub CommandButton1_Click()
Dim arrSelected() As String
Dim oCtr As Control
Dim lngIndex As Long
Dim lngCount As Long
Dim strReturn As String
lngCount = 0
For lngIndex = 1 To 4
Set oCtr = Me.Controls("Checkbox" & lngIndex)
If oCtr.Value = True Then
ReDim Preserve arrSelected(lngCount)
arrSelected(lngCount) = oCtr.Tag
lngCount = lngCount + 1
End If
Next
On Error GoTo Err_Empty
Select Case UBound(arrSelected)
Case 0
strReturn = arrSelected(0)
Case 1
strReturn = arrSelected(0) & " and " & arrSelected(1)
Case Else
strReturn = arrSelected(0)
For lngIndex = 1 To UBound(arrSelected) - 1
strReturn = strReturn & ", " & arrSelected(lngIndex)
Next lngIndex
strReturn = strReturn & " and " & arrSelected(UBound(arrSelected))
End Select
MsgBox strReturn
Err_ReEntry:
Exit Sub
Err_Empty:
Resume Err_ReEntry
End Sub

[WallIT]

Greg,

Thanks very much. This code works perfectly. Reading through the code I sort of understand what's happening. Its very clever and a huge help.

I'll be bookmarking your website for more tips! Thanks again - much appreciated.

[fumei]

As Greg has shown, using an array for all the checkbox values is the way to go. Also noteworth is how he deals with the last "and" (without the comma).

Bookmarking his site is a good idea.

If you are satisfied with the answer please mark the thread as Solved.