How to approach this situation

  • Thread starter Thread starter QB
  • Start date Start date
Q

QB

We currently have a 9 page report that gets filled in by our employees. In
it there are a number of similarily formatted tables with multiple rows and
columns. In each table there are 5 common columns entitled 1 through 5 which
corresponds to the inspection number.

What we are hoping, is that in the main heading (first page), when the user
enters the current inspection number (ie: 1 through 5) there would be a way
to lock out the other columns, so the employees could only edit the specific
column associated with the specified inspection number.

Can this be done? What would be the best approach?

Thank you for your expertise!

QB
 
QB,

This will not be possible using the basic word functionalities. Word does
have a feature (using Information Rights Management) where you can lock
sections of the document, but this is not an automated functionality and of
course you have overhead ($$$). Let us say you go ahead and use the IRM, it
is still not automatic. You will need some kind of very specialized
(programmed) Word Addin to do this.

I don't know if what I mentioned is more confusing. Given I program in
Office Automation and I don't know of any other method for what you are
looking for.

Shrini
 
Hi QB,

Assuming your document is created with formfields and the document is protected for forms, you could attach an on-exit macro to a
'key' formfield that the user uses to indicate which of the 5 columns is valid and, according to the option selected, enable only
the formfields in the corresponding column for data entry. In other words, all data entry in the table would be disabled until the
'key' formfield (possibly a drop-down formfield) has the relevant value entered.
 
An interesting puzzle, but not impossible. As macropod indicated you could
run a macro to enable only the fields in the appropriate columns of the
tables. With an unknown number of tables (9?) each with five columns
containing fields to be checked, the following macro will enable only the
fields in the column indicated. The column number here (iCol) is obtained
from a dropdown field 'Dropdown1', but you can modify that to suit your
form. The macro code can undoubtedly be simplified, but it works as shown

The macro will fail if some of the tables in your document have fewer than
five columns.


Dim iTable As Integer
Dim iCol As Integer
Dim iRow As Integer
Dim h, i As Long
iCol = ActiveDocument.FormFields("Dropdown1").Result

If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:=""
End If

For h = 1 To ActiveDocument.Tables.Count
With ActiveDocument.Tables(h)
iRow = .Rows.Count
Select Case iCol

Case Is = 1
For i = 1 To iRow
Set oCell = .Cell(i, 1).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = True
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 2).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 3).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 4).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 5).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i

Case Is = 2
For i = 1 To iRow
Set oCell = .Cell(i, 1).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 2).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = True
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 3).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 4).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 5).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i

Case Is = 3
For i = 1 To iRow
Set oCell = .Cell(i, 1).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 2).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 3).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = True
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 4).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 5).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i

Case Is = 4
For i = 1 To iRow
Set oCell = .Cell(i, 1).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 2).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 3).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = True
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 4).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 5).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i

Case Is = 5
For i = 1 To iRow
Set oCell = .Cell(i, 1).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 2).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 3).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 4).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = False
.Execute
End With
Next i
For i = 1 To iRow
Set oCell = .Cell(i, 5).Range
oCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
.Enable = True
.Execute
End With
Next i
End Select
End With

Next h

ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, _
NoReset:=True, _
Password:=""
 
On reflection the macro falls over when used with multiple tables (h).
I am going out so don't have time to find the problem now, but for a single
table lose the lines
For h = 1 To ActiveDocument.Tables.Count
and
Next h
and change
With ActiveDocument.Tables(h)
to
With ActiveDocument.Tables(1)
which does work.
I'll look into the multiple table issue later.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
It appears to fall over if the fields are not properly named when the macro
is run, so it should work as previously shown, however the macro can be
simplified

Dim iCol As Integer
Dim oTable As Table
Dim oRow As Row
Dim oCol As Column
Dim rCell As Range
iCol = ActiveDocument.FormFields("Dropdown1").Result
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:=""
End If
For Each oTable In ActiveDocument.Tables
With oTable
For Each oRow In .Rows
For Each oCol In .Columns
Set rCell = .Cell(oRow.Index, oCol.Index).Range
rCell.FormFields(1).Select
With Dialogs(wdDialogFormFieldOptions)
If oCol.Index = iCol Then
.Enable = True
Else
.Enable = False
End If
.Execute
End With
Next oCol
Next oRow
End With
Next oTable
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, _
NoReset:=True, _
Password:=""
ActiveDocument.Tables(1).Cell(1, iCol).Range.FormFields(1).Select


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
Back
Top