Two Questions

[Greg Maxey]

First, I posted the second question below using Google Groups earlier today. While it appears in Google Groups, it doesn't appear when I download messages from MSNEWS using Outlook Express. In fact, none of the dozens of messages I posted yesterday or today using Google Groups has appreared in MSNEWS that I can tell. Can one of you confirm or deny that you saw the second question posted today in your MSNEWS download?

If yes, the sorry for double posting. If not, is anyone else experiencing a problem with thier messages appearing in MSNEWS. Thanks

The second question.


Hello,

Today I am trying to write a procedure that will tally check boxes in a
table. I achieved my basic goal but want to come up with a more
universal method.


I thought I would simple evaluate each cell in each column of a table
and if the cell contained a checked box I would increment a counter and
put the result of that counter in the last cell of the column.


I knew that cells that did not contain a formfield or a checkbox would
generate an error. I thought I could work around that with an error
handler.


While it appears completely illogical, after the first error, the code
is incrementing the counter on cells that don't contain a checkbox. To
see this weird behaviour, you will need a Three column table with
several rows.


The first row is a header row e.g., Do you like? Yes No
The first column you can leave blank
In Columns 2 and 3 but check boxes leaving the last row blank
Check one or tow boxes in both columns
protect the form and run:


Sub WierdBehaviour()
Dim oTbl As Word.Table
Dim oCol As Column
Dim oCell As Cell
Dim i As Integer
Set oTbl = ActiveDocument.Tables(1)
ActiveDocument.Unprotect
For Each oCol In oTbl.Columns
i = 0
For Each oCell In oCol.Cells
On Error GoTo Handler
MsgBox oCell.RowIndex & " " & oCell.ColumnIndex
If oCell.Range.FormFields(1).CheckBox.Value = True Then
i = i + 1
End If
Skip:
Next oCell
Next oCol
ActiveDocument.Protect wdAllowOnlyFormFields, noReset:=True
Exit Sub
Handler:
Resume Skip
End Sub


As you will see, the cells in the first column are incrementing the
counter even though there is no formfield in those cells.


I must be missing something in the Error Handling but can't crack this
nut.


Thanks.

 

[Tony Jollans]

1. I haven't seen this post before

2. I just thought I'd take a quick look kind-of expecting it to be something
you must have overlooked - and find that I have overlooked it too. I don't
think it's anything to do with the error (you always get an error on row 1
but skipping row 1 doesn't solve it) but I have - on col 1, row 3, for
example

oCell.Range.FormFields.Count = 0
oCell.Range.FormFields(1).Name = "Check3"
etc.

I also have some other very funny stuff indeed like

oCell.Range.Text = chr(13) chr(2)

(I think chr(2) is a footnote or endnote rather than an end of cell).

It only appears to be a problem on column 1 - I added an extra to the msgbox
which highlights part of the problem:

Sub WierdBehaviour()
Dim oTbl As Word.Table
Dim oCol As Column
Dim oCell As Cell
Dim i As Integer
Set oTbl = ActiveDocument.Tables(1)
ActiveDocument.Unprotect
For Each oCol In oTbl.Columns
i = 0
For Each oCell In oCol.Cells
On Error GoTo Handler
MsgBox oCell.RowIndex & " " & oCell.ColumnIndex & vbCr & _
oCell.Range.FormFields(1).Range.Cells(1).RowIndex & " " & _
oCell.Range.FormFields(1).Range.Cells(1).ColumnIndex
If oCell.RowIndex = 1 Then GoTo Skip
If oCell.Range.FormFields(1).CheckBox.Value = True Then
Set fc = oCell.Range.FormFields(1)
i = i + 1
End If
Set oCell = Nothing
Skip:
Next oCell
Next oCol
ActiveDocument.Protect wdAllowOnlyFormFields, noReset:=True
Exit Sub
Handler:
Resume Skip
End Sub


I need a sleep!

--
Enjoy,
Tony

First, I posted the second question below using Google Groups earlier today.
While it appears in Google Groups, it doesn't appear when I download
messages from MSNEWS using Outlook Express. In fact, none of the dozens of
messages I posted yesterday or today using Google Groups has appreared in
MSNEWS that I can tell. Can one of you confirm or deny that you saw the
second question posted today in your MSNEWS download?

If yes, the sorry for double posting. If not, is anyone else experiencing a
problem with thier messages appearing in MSNEWS. Thanks

The second question.


Hello,

Today I am trying to write a procedure that will tally check boxes in a
table. I achieved my basic goal but want to come up with a more
universal method.

I thought I would simple evaluate each cell in each column of a table
and if the cell contained a checked box I would increment a counter and
put the result of that counter in the last cell of the column.

I knew that cells that did not contain a formfield or a checkbox would
generate an error. I thought I could work around that with an error
handler.

While it appears completely illogical, after the first error, the code
is incrementing the counter on cells that don't contain a checkbox. To
see this weird behaviour, you will need a Three column table with
several rows.

The first row is a header row e.g., Do you like? Yes No
The first column you can leave blank
In Columns 2 and 3 but check boxes leaving the last row blank
Check one or tow boxes in both columns
protect the form and run:

Sub WierdBehaviour()
Dim oTbl As Word.Table
Dim oCol As Column
Dim oCell As Cell
Dim i As Integer
Set oTbl = ActiveDocument.Tables(1)
ActiveDocument.Unprotect
For Each oCol In oTbl.Columns
i = 0
For Each oCell In oCol.Cells
On Error GoTo Handler
MsgBox oCell.RowIndex & " " & oCell.ColumnIndex
If oCell.Range.FormFields(1).CheckBox.Value = True Then
i = i + 1
End If
Skip:
Next oCell
Next oCol
ActiveDocument.Protect wdAllowOnlyFormFields, noReset:=True
Exit Sub
Handler:
Resume Skip
End Sub

As you will see, the cells in the first column are incrementing the
counter even though there is no formfield in those cells.

I must be missing something in the Error Handling but can't crack this
nut.

Thanks.

 

[Jezebel]

You're right about strange behaviour! You can control it using
oCell.Range.FormFields.Count -- that correctly returns 0 if the cell
contains no fields; but if if it returns zero, ocell.Range.Formfields(1)
none the less returns a reference to the first formfield in the ROW (if
there is one).

A couple of points on your coding --

1. There is no need to put 'on error goto Handler' within the loop -- it's
sufficient to put it once at the top of the code.

2. Don't use Integer data types. They are included only for backward
compatability. Since Windows is a 32 bit operating system, integers are just
longs with the top 16 bits zeroed out -- so there's no memory saving, and
extra overhead in zeroing out the top two bytes and checking for integer
overflows.


A simpler way to code it might be --

Dim pCheckCount() as long
Dim pCell as Word.Cell
Dim pTable as Word.Table

Set pTable = ActiveDocument.Tables(1)
Redim pCheckCount(1 to pTable.Columns.Count)
Set pCell = pTable.Cells(1,1)
Do
if pCell.Range.FormFields.Count > 0 then
if pCell.Range.FormFields(1).CheckBox.Value = True
pCheckCount(pCell.ColumnIndex) = pCheckCount(pCell.ColumnIndex)
+ 1
end if
end if
set pCell = pCell.Next
Loop until pCell is nothing






First, I posted the second question below using Google Groups earlier today.
While it appears in Google Groups, it doesn't appear when I download
messages from MSNEWS using Outlook Express. In fact, none of the dozens of
messages I posted yesterday or today using Google Groups has appreared in
MSNEWS that I can tell. Can one of you confirm or deny that you saw the
second question posted today in your MSNEWS download?

If yes, the sorry for double posting. If not, is anyone else experiencing a
problem with thier messages appearing in MSNEWS. Thanks

The second question.


Hello,

Today I am trying to write a procedure that will tally check boxes in a
table. I achieved my basic goal but want to come up with a more
universal method.

I thought I would simple evaluate each cell in each column of a table
and if the cell contained a checked box I would increment a counter and
put the result of that counter in the last cell of the column.

I knew that cells that did not contain a formfield or a checkbox would
generate an error. I thought I could work around that with an error
handler.

While it appears completely illogical, after the first error, the code
is incrementing the counter on cells that don't contain a checkbox. To
see this weird behaviour, you will need a Three column table with
several rows.

The first row is a header row e.g., Do you like? Yes No
The first column you can leave blank
In Columns 2 and 3 but check boxes leaving the last row blank
Check one or tow boxes in both columns
protect the form and run:

Sub WierdBehaviour()
Dim oTbl As Word.Table
Dim oCol As Column
Dim oCell As Cell
Dim i As Integer
Set oTbl = ActiveDocument.Tables(1)
ActiveDocument.Unprotect
For Each oCol In oTbl.Columns
i = 0
For Each oCell In oCol.Cells
On Error GoTo Handler
MsgBox oCell.RowIndex & " " & oCell.ColumnIndex
If oCell.Range.FormFields(1).CheckBox.Value = True Then
i = i + 1
End If
Skip:
Next oCell
Next oCol
ActiveDocument.Protect wdAllowOnlyFormFields, noReset:=True
Exit Sub
Handler:
Resume Skip
End Sub

As you will see, the cells in the first column are incrementing the
counter even though there is no formfield in those cells.

I must be missing something in the Error Handling but can't crack this
nut.

Thanks.

 

[Greg Maxey]

I need a sleep!

LOL, yeah I am pretty frazzled by all of this too.

 

[Jezebel]

Simpler still --

Dim pTable As Word.Table
Dim pCount() As Long
Dim pFormField As Word.FormField
Dim pColumn As Long

Set pTable = ActiveDocument.Tables(1)
ReDim pCount(1 To pTable.Columns.Count)
For Each pFormField In pTable.Range.FormFields
If pFormField.CheckBox.Value Then
pColumn = pFormField.Range.Cells(1).ColumnIndex
pCount(pColumn) = pCount(pColumn) + 1
End If
Next





First, I posted the second question below using Google Groups earlier today.
While it appears in Google Groups, it doesn't appear when I download
messages from MSNEWS using Outlook Express. In fact, none of the dozens of
messages I posted yesterday or today using Google Groups has appreared in
MSNEWS that I can tell. Can one of you confirm or deny that you saw the
second question posted today in your MSNEWS download?

If yes, the sorry for double posting. If not, is anyone else experiencing a
problem with thier messages appearing in MSNEWS. Thanks

The second question.


Hello,

Today I am trying to write a procedure that will tally check boxes in a
table. I achieved my basic goal but want to come up with a more
universal method.

I thought I would simple evaluate each cell in each column of a table
and if the cell contained a checked box I would increment a counter and
put the result of that counter in the last cell of the column.

I knew that cells that did not contain a formfield or a checkbox would
generate an error. I thought I could work around that with an error
handler.

While it appears completely illogical, after the first error, the code
is incrementing the counter on cells that don't contain a checkbox. To
see this weird behaviour, you will need a Three column table with
several rows.

The first row is a header row e.g., Do you like? Yes No
The first column you can leave blank
In Columns 2 and 3 but check boxes leaving the last row blank
Check one or tow boxes in both columns
protect the form and run:

Sub WierdBehaviour()
Dim oTbl As Word.Table
Dim oCol As Column
Dim oCell As Cell
Dim i As Integer
Set oTbl = ActiveDocument.Tables(1)
ActiveDocument.Unprotect
For Each oCol In oTbl.Columns
i = 0
For Each oCell In oCol.Cells
On Error GoTo Handler
MsgBox oCell.RowIndex & " " & oCell.ColumnIndex
If oCell.Range.FormFields(1).CheckBox.Value = True Then
i = i + 1
End If
Skip:
Next oCell
Next oCol
ActiveDocument.Protect wdAllowOnlyFormFields, noReset:=True
Exit Sub
Handler:
Resume Skip
End Sub

As you will see, the cells in the first column are incrementing the
counter even though there is no formfield in those cells.

I must be missing something in the Error Handling but can't crack this
nut.

Thanks.

 

[Tony Jollans]

What I see is that FormFields.Count returns 0 and FormFields(2) always
returns an error, but ...

FormFields(1) will return a reference to the first formfield in the next
cell in the same row as oCell if that cell (a) exists and (b) begins with a
formfield

I can't say precisely what happened but at one point in testing this I got a
corrupt VBA module - no great surprise perhaps but just thought I'd mention
it.

 

[Greg Maxey]

Tony,

With the your hint "b - begins with a formfield," I was able to create a
work around and basically make my original idea work. By preceeding each
checkbox with a single space, the following will tally and indicate checked
boxes in each colomn.

Sub WierdBehaviour()
Dim oTbl As Word.Table
Dim oCol As Column
Dim oCell As Cell
Dim i&, j&, oLastRow&
Dim bMarkCol As Boolean
Set oTbl = ActiveDocument.Tables(1)
oLastRow = oTbl.Rows.Count
On Error GoTo Handler
ActiveDocument.Unprotect
For Each oCol In oTbl.Columns
j = oCol.Index
bMarkCol = False
i = 0
For Each oCell In oCol.Cells
If oCell.Range.FormFields(1).CheckBox.Value = True Then
i = i + 1
bMarkCol = True
End If
Skip:
Next oCell
If bMarkCol Then
oTbl.Cell(oLastRow, j).Range.Text = i
End If
Next oCol
ActiveDocument.Protect wdAllowOnlyFormFields, noReset:=True
Exit Sub
Handler:
Resume Skip
End Sub


I realize the bMarkCol in the For Each loop is redundant, but I haven't
spent enough time or effort finding a better way.

Jezebel,

I haven't had a chance to fully explore your suggestions but plan to do so.
Thanks

I think that this has been a very puzzling and challenging exercise ;-).
Thaks for the help.

 

[Doug Robbins - Word MVP]

Hi Greg,

With the table setup as you describe and a couple of text form fields in the
final row of columns 2 and 3, if the following macro is set to run on exit
from each of the check box form fields in the second through the second last
rows of columns 2 and 3, the check boxes on each row will act as radio
buttons and the number of the checked check boxes in each column will be
displayed in the text form fields in the last row of the table. The user
must however use the tab key to move from check box to check box. The text
form fields should have the "Fill in enabled" box unchecked in their
properties dialog.

Dim i As Long, j As Long, k As Long, n As Long, m As Long, r As Long
i = Selection.Information(wdEndOfRangeRowNumber)
j = Selection.Information(wdEndOfRangeColumnNumber)
With Selection.Tables(1).Rows(i)
If .Cells(j).Range.FormFields(1).CheckBox.Value = True Then
For k = 2 To .Cells.Count
If k <> j Then
.Cells(k).Range.FormFields(1).CheckBox.Value = False
End If
Next
Else
For k = 2 To .Cells.Count
If k <> j Then
.Cells(k).Range.FormFields(1).CheckBox.Value = True
End If
Next
End If
End With
With Selection.Tables(1)
For n = 2 To 3
r = 0
For m = 2 To .Rows.Count - 1
If .Cell(m, n).Range.FormFields(1).CheckBox.Value = True Then
r = r + 1
End If
Next m
.Cell(.Rows.Count, n).Range.FormFields(1).result = r
Next n
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

First, I posted the second question below using Google Groups earlier today.
While it appears in Google Groups, it doesn't appear when I download
messages from MSNEWS using Outlook Express. In fact, none of the dozens of
messages I posted yesterday or today using Google Groups has appreared in
MSNEWS that I can tell. Can one of you confirm or deny that you saw the
second question posted today in your MSNEWS download?

If yes, the sorry for double posting. If not, is anyone else experiencing a
problem with thier messages appearing in MSNEWS. Thanks

The second question.


Hello,

Today I am trying to write a procedure that will tally check boxes in a
table. I achieved my basic goal but want to come up with a more
universal method.

I thought I would simple evaluate each cell in each column of a table
and if the cell contained a checked box I would increment a counter and
put the result of that counter in the last cell of the column.

I knew that cells that did not contain a formfield or a checkbox would
generate an error. I thought I could work around that with an error
handler.

While it appears completely illogical, after the first error, the code
is incrementing the counter on cells that don't contain a checkbox. To
see this weird behaviour, you will need a Three column table with
several rows.

The first row is a header row e.g., Do you like? Yes No
The first column you can leave blank
In Columns 2 and 3 but check boxes leaving the last row blank
Check one or tow boxes in both columns
protect the form and run:

Sub WierdBehaviour()
Dim oTbl As Word.Table
Dim oCol As Column
Dim oCell As Cell
Dim i As Integer
Set oTbl = ActiveDocument.Tables(1)
ActiveDocument.Unprotect
For Each oCol In oTbl.Columns
i = 0
For Each oCell In oCol.Cells
On Error GoTo Handler
MsgBox oCell.RowIndex & " " & oCell.ColumnIndex
If oCell.Range.FormFields(1).CheckBox.Value = True Then
i = i + 1
End If
Skip:
Next oCell
Next oCol
ActiveDocument.Protect wdAllowOnlyFormFields, noReset:=True
Exit Sub
Handler:
Resume Skip
End Sub

As you will see, the cells in the first column are incrementing the
counter even though there is no formfield in those cells.

I must be missing something in the Error Handling but can't crack this
nut.

Thanks.

 

[Greg Maxey]

Doug. Nice work. The problems always goes back to the user having to tab
through the form to fire the macros :-(

 

[Greg Maxey]

Jezebel,

I got close. How would you modify so that the last row is left blank if
there are no checkboxes in the column?

Sub JezebelsTally()
Dim pTable As Word.Table
Dim pCount() As Long
Dim pFormField As Word.FormField
Dim pColumn&, oLastRow&, i&
Set pTable = ActiveDocument.Tables(1)
oLastRow = pTable.Rows.Count
ActiveDocument.Unprotect
ReDim pCount(1 To pTable.Columns.Count)
For Each pFormField In pTable.Range.FormFields
If pFormField.CheckBox.Value Then
pColumn = pFormField.Range.Cells(1).ColumnIndex
pCount(pColumn) = pCount(pColumn) + 1
End If
Next
For i = 1 To pTable.Columns.Count
pTable.Cell(oLastRow, i).Range.Text = pCount(i)
Next i
ActiveDocument.Protect wdAllowOnlyFormFields, noReset:=True
End Sub

 

[Jezebel]

Either --

For i = 1 To pTable.Columns.Count

if pCount(i) > 0 then
pTable.Cell(oLastRow, i).Range.Text = pCount(i)
end if

Next i

Or, if you might be running this multiple times and the checkboxing is
dynamic (seems unlikely) --

For i = 1 To pTable.Columns.Count

if pCount(i) > 0 then
pText = ""
else
pText = pCount(i)
end if
pTable.Cell(oLastRow, i).Range.Text = pText

Next

Maybe using DocVariable fields would be better still -- then your code
doesn't need to make assumptions about how or where you want the results to
appear.

 

[Greg Maxey]

Jezebel,

I am sorry, I do want 0 to appear at the end of columns with checkboxes
where none are checked but in columns with no checkboxes I don't what
anything to appear.

I only looked at your first suggestion before replying but will look at the
second now to see if I can work it out with that.

Thanks.

 

[Jezebel]

ah, that makes sense. You could use a ColumnFlag array --

Dim pColumnCount as long 'Use a variable since you need it
several times
Dim pCount() as long
Dim pColumnFlags() as boolean

:
pColumnCount = pTable.Columns.Count

ReDim pCount(1 To pColumnCount )
Redim pColumnFlags(1 to pColumnCount)

For Each pFormField In pTable.Range.FormFields
pColumn = pFormField.Range.Cells(1).ColumnIndex
pColumnFlags(pColumn) = true
If pFormField.CheckBox.Value Then
pCount(pColumn) = pCount(pColumn) + 1
End If
Next


For i = 1 To pColumnCount
if pColumnFlags(i) then
pTable.Cell(oLastRow, i).Range.Text = pCount(i)
end if
Next

 

[Greg Maxey]

Jezebel,

Perfect. I knew I was going to need a boolean, but I didn't know if it
would be a separate array or part of a two dimensional array. Either way I
needed the help. Arrays still shiver my timbers but not not as bad as
classes do ;-). Here is what I am using now and it seems to work:

Sub JezebelsTally()
Dim pTable As Word.Table
Dim pCount() As Long
Dim pFormField As Word.FormField
Dim pColumnCount&, oLastRow&, pColumn&, i&
Dim pColumnFlags() As Boolean
Set pTable = ActiveDocument.Tables(1)
pColumnCount = pTable.Columns.Count
oLastRow = pTable.Rows.Count
ReDim pCount(1 To pColumnCount)
ReDim pColumnFlags(1 To pColumnCount)
ActiveDocument.Unprotect
For Each pFormField In pTable.Range.FormFields
pColumn = pFormField.Range.Cells(1).ColumnIndex
pColumnFlags(pColumn) = True
If pFormField.CheckBox.Value Then
pCount(pColumn) = pCount(pColumn) + 1
End If
Next
For i = 1 To pColumnCount
If pColumnFlags(i) Then
pTable.Cell(oLastRow, i).Range.Text = pCount(i)
End If
Next
ActiveDocument.Protect wdAllowOnlyFormFields, noReset:=True
End Sub