Solved: Check boxes in a table (extract rows where checkboxes are ON)

[Anita71]

Hi,

I have a table with 4 columns. The first column contains the check box. I want to extract ( into an other sheet or document) all the rows where the checkboxes were enabled by user.
Can you help me?
Thanks,

Anita

[Jacob Hilderbrand]

Try this.
[vba]
Option Explicit

Sub Macro1()

Dim i As Long
Dim Doc As Document
Dim NewDoc As Document
Dim SelectedText As Collection
Dim Temp As String

Set Doc = ActiveDocument
Set SelectedText = New Collection
For i = 1 To Doc.FormFields.Count
With Doc.FormFields(i)
If .Type = wdFieldFormCheckBox Then
If .CheckBox.Value = True Then
Temp = Selection.Tables(1).Cell(i, 2).Range.Text & vbTab & _
Selection.Tables(1).Cell(i, 3).Range.Text & vbTab & _
Selection.Tables(1).Cell(i, 4).Range.Text
Temp = Replace(Temp, Chr(7), "")
Temp = Replace(Temp, Chr(13), "")
SelectedText.Add Temp
End If
End If
End With
Next i
If SelectedText.Count > 0 Then
Set NewDoc = Documents.Add
NewDoc.Activate
For i = 1 To SelectedText.Count
Selection.TypeText Text:=SelectedText(i)
Selection.TypeParagraph
Next i
End If

End Sub
[/vba]
Refer to the attachment for more information.

[Anita71]

THANK YOU!

It's perfect :o)

Anita

[Jacob Hilderbrand]

You're Welcome

Take Care

[Anita71]

HI,
Thanks again for your support.
I don't know if I can ask you one more thing....
I tried to modify your macro to create a new document from a template...like this:
Set NewDoc = Documents.Add Template:= "C:\forms\DOU.dot"
>It does not work!
Is there a way to simply modify your macro?

Thank you,
Anita

[Jacob Hilderbrand]

You can just open the template and get a new document.

[VBA]Set Doc = Documents.Open(Filename:="C:\forms\DOU.dot")[/VBA]

[TonyJollans]

Errmm, Jake, opening a template just opens the template, it does not create a new document.

Your problem, Anita, is just that you need parentheses round the parameter, thus:
[VBA]Set NewDoc = Documents.Add(Template:= "C:\forms\DOU.dot")[/VBA]

[Jacob Hilderbrand]

Thanks Tony.

[fumei]

I would just like to point out:

1. the code uses:

Selection.Tables(i) - which assumes that the Selection IS, in fact, in a table - and it may not be.

2. Formfields(i) makes an assumption that there are no other checkboxes, other than in the table. Of course for this document, that MAY be true, but I don't know that.

3. If the formfields are truly functioning as formfields, then the code should unprotect, and reprotect the document.

The following does a specific test for CHECKED checkboxes WITHIN tables, and also checks that the adjacent cells in the row actually have text - although maybe you want enpty text....I just thought it may add a bit more error trapping.

[vba]Sub CheckedRows()

Dim myFF As FormField
Dim var
Dim NewString As String
Dim OtherDoc As Document
ActiveDocument.Unprotect Password:=""
For Each myFF In ActiveDocument.FormFields
If myFF.Type = 71 And myFF.CheckBox.Value = True Then
myFF.Select
' checks to see if it is, in fact, in a table
If Selection.Information(wdWithInTable) Then
For var = 1 To 3
Selection.MoveRight unit:=wdCell
If Selection.Text <> "" Then
NewString = NewString & _
Selection.Text & vbTab
End If
Next var
NewString = NewString & vbCrLf
End If
End If
Next
If NewString <> "" Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:=""
Set OtherDoc = Documents.Add(Template:="C:\forms\DOU.dot")
Selection.TypeText Text:=NewString
End If
End Sub[/vba]

Ooops, and you should always of course Set your documents = Nothing as well.

[geekgirlau]

Gerry, I haven't tested this in 2003, but in previous versions unprotecting then reprotecting the document actually clears any values you have set in form fields!

[MOS MASTER]

Gerry, I haven't tested this in 2003, but in previous versions unprotecting then reprotecting the document actually clears any values you have set in form fields!

Hi,

In Word 2000 it didn't matter if you where doing this by hand or by code al fields where empy after a unprotect/protect action.

With this code you can prevent that:[VBA]
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="Test"
[/VBA]The NoReset is the crusial part in this codeline.

From 2002 and upwards the Interface part off Word got fixed in the way you could protect and unprotect (Via the Form toolbar) as much as you liked the value stayed...

But still in 2002 or 2003 when you execute a code like:[VBA]Sub test()
ActiveDocument.Unprotect
'do stuff
ActiveDocument.Protect wdAllowOnlyFormFields
End Sub

[/VBA]The values of the formfields get emptied (And if you ask me I don't think it's a bug at all but just a means to reset the values so the 'default' values of the Formfield can be set)

So yes you always have to use the NoReset property of the protect method. (If you do it by VBA)

Enjoy!

[Anita71]

Thanks all of you for your help
Here is what I've done...... I'm glad I could adapt it

Dim Doc As Document
Dim NewDoc As Document
Dim SelectedText As Collection
Dim Temp As String
Set Doc = ActiveDocument
If Selection.Information(wdWithInTable) = False Then
MsgBox "The insertion point is not in the table. Please, put your cursor into the Services Table"
End
End If

Set SelectedText = New Collection
For i = 1 To Doc.FormFields.Count
With Doc.FormFields(i)
If .Type = wdFieldFormCheckBox Then
If .CheckBox.Value = True Then
Temp = Selection.Tables(1).Cell(i, 2).Range.Text & vbTab & _
Selection.Tables(1).Cell(i, 3).Range.Text & vbTab & _
Selection.Tables(1).Cell(i, 4).Range.Text
Temp = Replace(Temp, Chr(7), "")
Temp = Replace(Temp, Chr(13), "")
SelectedText.Add Temp
End If
End If
End With

Next i
If SelectedText.Count > 0 Then
Set NewDoc = Documents.Add(Template:="C:\Documents and Settings\.........\Selected Services.dot")
'Set NewDoc = Documents.Add

NewDoc.Activate
Selection.GoTo What:=wdGoToBookmark, Name:="test"

For i = 1 To SelectedText.Count
Selection.TypeText Text:=SelectedText(i)
Selection.TypeParagraph
Next i

End If
End Sub

[MOS MASTER]

Hi Anita,

Please use [ VBA ] [ /VBA ] (Without spaces) tags arround you're code. This makes the reading a lot easier..

Some minor point you could consider added to the code. [VBA]
Option Explicit 'using this forces you to declare you're variables!
'write as first line in the code module.
Sub TT()
Dim Doc As Document
Dim NewDoc As Document
Dim SelectedText As Collection
Dim Temp As String
Dim i As Integer 'declare all variables
Set Doc = ActiveDocument
If Selection.Information(wdWithInTable) = False Then
MsgBox "The insertion point is not in the table. Please, " & _
"put your cursor into the Services Table"
End
End If
Set SelectedText = New Collection
For i = 1 To Doc.FormFields.Count
With Doc.FormFields(i)
If .Type = wdFieldFormCheckBox Then
If .CheckBox.Value = True Then
Temp = Selection.Tables(1).Cell(i, 2).Range.Text & vbTab & _
Selection.Tables(1).Cell(i, 3).Range.Text & vbTab & _
Selection.Tables(1).Cell(i, 4).Range.Text
Temp = Replace(Temp, Chr(7), "")
Temp = Replace(Temp, Chr(13), "")
SelectedText.Add Temp
End If
End If
End With
Next i

If SelectedText.Count > 0 Then
Set NewDoc = Documents.Add(Template:="C:\Documents and Settings\.........\Selected Services.dot")

NewDoc.Activate
Selection.GoTo What:=wdGoToBookmark, Name:="test"

For i = 1 To SelectedText.Count
Selection.TypeText Text:=SelectedText(i)
Selection.TypeParagraph
Next i
End If
'Clean up
Set Doc = Nothing
Set SelectedText = Nothing
Set NewDoc = Nothing
End Sub
[/VBA]
Gruβ