FormFields (Add row to protected table)

[spunkymuffmonkey]

Hi,

Using the VBA code at the end of this post I am now able to prompt users if
they want to add a row to the bottom of a protected table when they reach the
last row.

The macro will insert form fields into this new row but I require one of the
form fields in column 4 of the new row to have its properties set
specifically to perform a calculation, the calculation being the value of the
2nd column in the row multiplied by the value in the 3rd column of the row.

I cannot work out how to change the new fields properties to what I need
them to be.

I can e-mail a copy of the document if none of the above makes any sense.

VBA code:

Sub addrow()

response = MsgBox("Add new row?", vbQuestion + vbYesNo)
If response = vbYes Then
ActiveDocument.Unprotect Password:="bob"

Selection.InsertRowsBelow 1
Selection.Collapse (wdCollapseStart)
myRow = Selection.Information(wdStartOfRangeRowNumber)

Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text1row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text2row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text3row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text4row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text5row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text6row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.Name = "text7row" & myRow
.Enabled = True
.ExitMacro = "addrow"
End With
myNewField = "text1row" & myRow
ActiveDocument.Protect NoReset:=True, Password:="bob",
Type:=wdAllowOnlyFormFields
ActiveDocument.Range.FormFields(myNewField).Select
End If

End Sub

Many thanks for looking

 

[Graham Mayor]

Your code (from which I may borrow) lacks only a few lines to make it do
what you want. In particular you need to record the new names of the fields
in col2 and col3 and apply those names to the calculation in col4. You then
need to add the calculate on exit check to Col3 The field type in col 4
needs to be set as a calculated field type

Sub addrow()
Dim Col2Name As Variant
Dim Col3Name As Variant
Dim Response As String

Response = MsgBox("Add new row?", vbQuestion + vbYesNo)
If Response = vbYes Then
ActiveDocument.Unprotect Password:="bob"
Selection.InsertRowsBelow 1
Selection.Collapse (wdCollapseStart)
myRow = Selection.Information(wdStartOfRangeRowNumber)
Selection.FormFields.Add Range:=Selection.Range, _
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.name = "text1row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range, _
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.name = "text2row" & myRow
'***********************************
Col2Name = .name
'***********************************
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range, _
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.name = "text3row" & myRow
'***********************************
Col3Name = .name
.Enabled = True
.CalculateOnExit = True
'***********************************
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range, _
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.name = "text4row" & myRow
'***********************************
.Enabled = False
.TextInput.EditType Type:=wdCalculationText, _
Default:="=" & Col2Name & " + " & Col3Name, Format:=""
'***********************************
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range, _
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.name = "text5row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range, _
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.name = "text6row" & myRow
.Enabled = True
End With
Selection.MoveRight Unit:=wdCell
Selection.FormFields.Add Range:=Selection.Range, _
Type:=wdFieldFormTextInput
myCount = ActiveDocument.Range.FormFields.Count
With ActiveDocument.FormFields(myCount)
.name = "text7row" & myRow
.Enabled = True
.ExitMacro = "addrow"
End With
myNewField = "text1row" & myRow
ActiveDocument.Protect NoReset:=True, Password:="bob", _
Type:=wdAllowOnlyFormFields
ActiveDocument.Range.FormFields(myNewField).Select
End If
End Sub


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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[spunkymuffmonkey]

Hi Graham,

Many many thanks indeed for your clever input, your answer is just what I
desired.

Thanks again.