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) With ActiveDocumentResponse = 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 .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 |
Thursday, September 8, 2011
Add a row to a table in a protected form
Labels:
MACROS
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment