Solved: Hiding rows without input

[efto]

Hi there!
Can somebody help poor newbie modifying this macro? I put together some pieces of code found on this forum like d-r Frankenstein but my monster is not alive. What I am trying to do is to hide rows without input. If the row contains formfields, they need to be checked for input and if all of them are without input the row is hidden. The row is supposed to be hidden if the input is “ “(10 spaces) also.
Here is the macro which proves my lack of knowledge, but also proves that I keep trying.
Efto


Sub CheckFF()
Dim myTable As Table, myRow As Range, myForm As FormField, _
TextInRow As Boolean
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:=""
End If
For Each myTable In ActiveDocument.Tables
Set myRow = myTable.Rows(1).Range
NumRows = myTable.Rows.Count
Application.ScreenUpdating = False
For Counter = 1 To NumRows
TextInRow = False
For Each myForm In ActiveDocument.FormFields
If myForm.Type = wdFieldFormCheckBox Then
If myForm.CheckBox.Value = True Then
TextInRow = True
Exit For
End If
End If
If myForm.Result <> "" Then
If myForm.Result <> " " Then
TextInRow = True
Exit For
End If
TextInRow = True
Exit For
End If
Next myForm
If TextInRow Then
Set myRow = myRow.Next(wdRow)
Else
With myRow
.Font.Hidden = True
End With
End If
Next Counter
Next myTable
' ReProtect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True
End If
End Sub

[fumei]

Could you clarify: For Each myForm In ActiveDocument.FormFields
If myForm.Type = wdFieldFormCheckBox Then
If myForm.CheckBox.Value = True Then
TextInRow = True
Exit For Say you have three formfields; a checkbox, followed by a text formfield, followed by a text formfield. If the checkbox value is true then you exit the For, and the two text formfields are never looked at. Is this correct?

[efto]

Hi there!

Say you have three formfields; a checkbox, followed by a text formfield, followed by a text formfield. If the checkbox value is true then you exit the For, and the two text formfields are never looked at. Is this correct?
Yes it is correct, if it is found AT LEAST ONE form field with input then the row is unhidden.

I will try to explain more precisely.

I have a document which sometimes contains one table and sometimes multiple tables, depending on the user input. Inside the tables there are different types of form fields: check boxes, text form fields, drop-down form fields without strict order. Sometimes the row contains no form fields, sometimes maybe three. So the macro should do the following – to check each row for presence of form fields. If form fields are found their values and results should be checked. In case ALL of them are without input (check boxes – false, text and drop-downs empty) the font of the row is set to hidden. If it is found AT LEAST ONE form field with input (but different from “ “(10 spaces) the font of the row is set to unhidden.
So the point is to make the document more compact. I will add a toolbar with commands “Hide empty fields” and “Show empty fields”. After filling the form the user will click on “Hide empty fields” (if he does not the macro will be triggered on close). But later if he changes his mind and has additional info he will click “Show empty fields” and fill in the form fields that were empty.
I hope that I explained my intention in not so confusing way.

Efto

[fumei]

The logic still doesn't seem right to me. If the first formfield happens to be a checkbox, and it is True, the others are NOT checked. NONE of the others are checked. It exits the For. So no formfield in any other row is checked either.
So the macro should do the following – to check each row for presence of form fields. What I am saying is that if you have:

Row 1: Checkbox Textbox TextBox
Row 2: Textbox Textbox Textbox
Row 3: Checkbox Textbox Textbox
Row 4: Textbox Checkbox Textbox

If Row 1 Checkbox = True, NO other formfield is looked at, NO other row is looked at. If that first Checkbox = True, you exit the For. It does NOT check each row. If that first one is True...it is outta there.

Now maybe this situation will never occur, if which case, it does not matter. However, at least from what you say, I am not sure the logic is correct.

[mdmackillop]

Hi Efto,
Try the following. It checks each row, so should get round Gerry's concern.
Regards
MD

Sub CheckFF()
Dim myTable As Table, myRow As Range, myForm As FormField, _
TextInRow As Boolean
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:=""
End If
For Each myTable In ActiveDocument.Tables
numrows = myTable.Rows.Count
Application.ScreenUpdating = False
For counter = 1 To numrows
TextInRow = False
Set myRow = myTable.Rows(counter).Range
For Each myForm In myRow.FormFields
If myForm.Type = wdFieldFormCheckBox Then
If myForm.CheckBox.Value = True Then
TextInRow = True
Exit For
End If
Else
If myForm.Result <> "" And myForm.Result <> "0" Then
If myForm.Result <> " " Then
TextInRow = True
Exit For
End If
TextInRow = True
Exit For
End If
End If

Next myForm
If Not (TextInRow) Then myRow.Font.Hidden = True
Next counter
Next myTable
' ReProtect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True
End If
End Sub

[fumei]

Ahhhhh, true. That will check each row, but again I have to ask, does it really turn on that first checkbox?

Row X: Checkbox Textbox Textbox

Still, if the checkbox is True, nothing is checked in ANY formfields following in that row. I am not saying they should be - it is not my document - but it just seems funny to me. I would think if this is some sort of validation logic, then ALL elements are to be validated.

What is so special about the checkbox?

[mdmackillop]

Hi Gerry,
The purpose is not validation but to change all fonts in a row to hidden if there is no text or ticked checkbox in a row. This allows the row to be hidden. The logic as I see it is that as soon as you find anything in the row, the row will not be hidden so you can stop checking.
I see MWE has opened a related questions on hiding rows and columns.

[fumei]

I still fail to see WHY it is desireable.

[mdmackillop]

If a cross in a CheckBox has set TextInRow to True, there is little point in analysing a bunch of other cells where the only possible result is to repeat the setting.

[efto]

The purpose is not validation but to change all fonts in a row to hidden if there is no text or ticked checkbox in a row. This allows the row to be hidden. The logic as I see it is that as soon as you find anything in the row, the row will not be hidden so you can stop checking.

Thanks mdmackillop, you explained better than me.
Thank you very much for your help, but there is a problem with fileds containing " " (10 spaces). The rows are not hidden. I uploaded the file, so can you can check it, please?

[efto]

Hi again,
You do not have to check the file, unless you really want. I found the solution. Instead of setting initial value
TextInRow = False
I did
TextInRow = True
And so on...

This way when the row contains some text but does not contain any form field, it is unhidden. Here it is:


Sub HideRows()

Dim myTable As Table, myRow As Range, myForm As FormField, _
TextInRow As Boolean
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:=""
End If
For Each myTable In ActiveDocument.Tables
NumRows = myTable.Rows.Count
Application.ScreenUpdating = False
For Counter = 1 To NumRows
TextInRow = True
Set myRow = myTable.Rows(Counter).Range
For Each myForm In myRow.FormFields
If myForm.Type = wdFieldFormCheckBox Then
If myForm.CheckBox.Value = False Then
TextInRow = False
Exit For
End If
Else
If myForm.Result = "" Or myForm.Result = "0" Or _
myForm.Result = " " Then
TextInRow = False
Exit For
End If
End If

Next myForm
If Not (TextInRow) Then myRow.Font.Hidden = True
Next Counter
Next myTable
' ReProtect the document.
If ActiveDocument.ProtectionType = wdNoProtection Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True
End If
End Sub

Again, thank you very much!

Edited: I can not set this thread to solved, please help.

[TonyJollans]

I haven't read the whole of this thread but I feel I should point out that hidden FormFields should generally be avoided. When tabbing through FormFields in a (protected) Form users will be taken to the hidden fields and, on apparently losing their cursor, they will be ringing the help desk asking what has gone wrong.

[fumei]

Thank you Tony.

[efto]

I haven't read the whole of this thread but I feel I should point out that hidden FormFields should generally be avoided. When tabbing through FormFields in a (protected) Form users will be taken to the hidden fields and, on apparently losing their cursor, they will be ringing the help desk asking what has gone wrong.

I started new thread regarding tabbing through hidden form fields here: http://vbaexpress.com/forum/showthread.php?t=7647

Please, can somebody mark this thread solved because I can not?