Thursday, September 8, 2011

Add a row to a table in a protected form



In a recent Word forum question, a user wanted to automatically provide the function to add a row to a table in a protected form and to fill the new row with form fields to match those in the previous row, but with new field bookmark names and a calculation in the final cell to add the other fields in the row. The user had done most of the work, which I have borrowed for this example, but was having problems with the calculation.

The modified solution works in a table with seven columns. The content of the fields in the first six columns is added in the final column.

Sub addrow()
'Works with a seven column table
Dim oTable As Table
Dim Response As String
Dim CurRow As Long
Dim i As Long
Dim fCount As Long
Dim sPassword as String

Set oTable = ActiveDocument.Tables(1)
sPassword = "" 'Define the password used to protect the form (if any)
Response = MsgBox("Add new row?", vbQuestion + vbYesNo)
If Response = vbYes Then
     ActiveDocument.Unprotect Password:="sPassword" 'Unprotect document
     Selection.InsertRowsBelow 1 'Add a row to the bottom of the table
     Selection.Collapse (wdCollapseStart) 'Put the cursor in the first cell of the new row
     CurRow = Selection.Information(wdStartOfRangeRowNumber) 'Read the number of the new row

     For i = 1 To oTable.Columns.Count
          oTable.Cell(CurRow, i).Select 'Select the next cell for processing
          Selection.FormFields.Add Range:=Selection.Range, _
          Type:=wdFieldFormTextInput 'And add a form field
          fCount = ActiveDocument.Range.FormFields.Count
          With ActiveDocument.FormFields(fCount)
               .Name = "col" & i & "row" & CurRow 'Add a unique bookmark name
               .Enabled = True 'Enable the field for user entry
               .CalculateOnExit = True 'set the calculate on exit check box
               If i = 6 Then .ExitMacro = "addrow" 'add this macro to the cell in column 6
               If i = 7 Then 'add a calculation to add the field results in cols 1 to 6
                    .Enabled = False
                    .TextInput.EditType Type:=wdCalculationText, _
                    Default:="=Col1Row" & CurRow _
                    & " + Col2Row" & CurRow _
                    & " + Col3Row" & CurRow _
                    & " + Col4Row" & CurRow _
                    & " + Col5Row" & CurRow _
                    & " + Col6Row" & CurRow, _
                    Format:=""
               End If
          End With
     Next
i
 
    With ActiveDocument
          .Protect NoReset:=True, Password:="sPassword", _
          Type:=wdAllowOnlyFormFields 'Reprotect the form
          .Range.FormFields("col1row" & CurRow).Select 'Select the first field in the new row
     End With
End If
End Sub


No comments:

Post a Comment