PDA

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



Anita71
02-09-2005, 03:29 AM
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:help

Jacob Hilderbrand
02-09-2005, 04:20 AM
Try this.

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

Refer to the attachment for more information.

Anita71
02-09-2005, 09:10 AM
THANK YOU!

It's perfect :o)

Anita

Jacob Hilderbrand
02-09-2005, 03:24 PM
You're Welcome :beerchug:

Take Care

Anita71
04-07-2005, 05:33 AM
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
04-07-2005, 07:22 AM
You can just open the template and get a new document.

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

TonyJollans
04-07-2005, 08:14 AM
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:
Set NewDoc = Documents.Add(Template:= "C:\forms\DOU.dot")

Jacob Hilderbrand
04-07-2005, 08:44 AM
Thanks Tony. :)

fumei
04-08-2005, 12:33 PM
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.

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

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

geekgirlau
04-11-2005, 07:12 PM
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
04-12-2005, 11:26 AM
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, :D

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:
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="Test"
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:Sub test()
ActiveDocument.Unprotect
'do stuff
ActiveDocument.Protect wdAllowOnlyFormFields
End Sub

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! :thumb

Anita71
04-14-2005, 05:38 AM
Thanks all of you for your help:friends:
Here is what I've done...... I'm glad I could adapt it :cloud9:

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

:beerchug:

MOS MASTER
04-14-2005, 12:24 PM
Hi Anita,

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

Some minor point you could consider added to the code.
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

Gruβ :whistle: