Calculation Form Field

[spunkymuffmonkey]

Hi everyone.

I have hit a problem with a protected form I'm working with.

I have a macro that allows users to add a new row to a table within the
protected form, the macro adds formfields in to the new row, one of which is
a calculation field.

This calcuation field simply multiplies the previous two fields contents
using cell references (i.e. b1 * c1, however this unfortunately does not seem
to work when the form is reprotected and contents entered into b1/c1 etc.

I have checked the expression that I am generating to the new field, and
that calculate on exit is set to true, but no joy.

The funny thing is that when I unlock the form then check the calcualtion
field properties, and then click OK, the calculation field displays the right
result.

Below is the code I'm using, any help would be greatly appreciated.

Private Sub CommandButton1_Click()

Dim oTable As Table
Dim sPassword, sDefault As String
Dim iRowCount As Integer

Application.ScreenUpdating = False

sPassword = "Gatekeeper1"

With ActiveDocument
If .ProtectionType <> wdNoProtection Then
.Unprotect sPassword
End If
End With

Set oTable = ActiveDocument.Tables(6)

oTable.Rows.Add
iRow = oTable.Rows.Count

oTable.Cell(iRow, 1).Select
Selection.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
oTable.Cell(iRow, 1).Select
With Selection.FormFields(1)
With .TextInput
.EditType wdRegularText, Format:="First capital"
End With
End With
oTable.Cell(iRow, 2).Select
Selection.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
oTable.Cell(iRow, 2).Select
With Selection.FormFields(1)
.CalculateOnExit = True
With .TextInput
.EditType wdNumberText, Format:="0"
End With
End With
oTable.Cell(iRow, 3).Select
Selection.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
oTable.Cell(iRow, 3).Select
With Selection.FormFields(1)
.ExitMacro = "UpdateField"
.CalculateOnExit = True
With .TextInput
.EditType wdNumberText, Format:="0"
End With
End With
oTable.Cell(iRow, 4).Select
Selection.FormFields.Add Range:=Selection.Range, Type:=wdFieldFormTextInput
oTable.Cell(iRow, 4).Select
iRowCount = oTable.Rows.Count
sDefault = "=b" & iRowCount & "*" & "c" & iRowCount
With Selection.FormFields(1)
.TextInput.EditType Type:=wdCalculationText, Format:="",
Enabled:=False
.CalculateOnExit = True
.TextInput.Default = sDefault
End With
oTable.Cell(iRow, 5).Select
Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
oTable.Cell(iRow, 5).Select
With Selection.FormFields(1)
With .TextInput
.EditType wdRegularText, Format:="First capital"
End With
End With
oTable.Cell(iRow, 6).Select
Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
oTable.Cell(iRow, 6).Select
With Selection.FormFields(1)
With .TextInput
.EditType wdRegularText, Format:="First capital"
End With
End With
oTable.Cell(iRow, 7).Select
Selection.FormFields.Add Range:=Selection.Range,
Type:=wdFieldFormTextInput
oTable.Cell(iRow, 7).Select
With Selection.FormFields(1)
With .TextInput
.EditType wdRegularText, Format:="Title Case"
End With
End With

oTable.Cell(iRow, 1).Select
Unload Me
ActiveDocument.Protect wdAllowOnlyFormFields, Password:=sPassword,
noreset:=True

Application.ScreenUpdating = True
End Sub

 

[MS Public]

Does the following make any difference?

With Selection.FormFields(1)
.TextInput.EditType Type:=wdCalculationText, Default:=sDefault,
Format:="", Enabled:=False
.CalculateOnExit = True
End With

Peter Jamieson

 

[Doug Robbins - Word MVP]

The .CalculateOnExit = True

needs to be set for the formfields that contribute to the calculation, not
the one that performs the calculation.

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

 

[spunkymuffmonkey]

Thanks for the info, the contributing fields are set to calc on exit, but
still no joy!

 

[spunkymuffmonkey]

Unfortunately not, but thanks for your suggestion!

 

[spunkymuffmonkey]

Hi, have solved the issue by inserting a field instead of a formfield, just
to let you know in case you're trying to find a solution.

Many thanks again for your guidance.

 

[spunkymuffmonkey]

Hi, have solved the issue by inserting a field instead of a formfield, just
to let you know in case you're trying to find a solution.

Many thanks again for your guidance.

 

[Graham Mayor]

It should work if you multiplied the bookmark names of the cells, however
then you need to have more control over your cell naming, as rows are being
added - see the two examples at http://www.gmayor.com/word_vba_examples.htm

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


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

 

[Peter Jamieson]

FWIW I had exactly the same problem as the OP when I tried his code, but
outside the context of a userform, on Word 2007 SP2 on Vista.
The first thing I tried was to change the calculation from using cell
addresses to formfield names, but it made no difference. What did makeit
work was the change I mentioned earlier - that works here with either
type of addressing. Precisely why is not clear to me, but all I did was
to use the method as documented rather than as copied from the OP's
code. But perhaps you are seeing something else?

Anyway the OP seems to have found a satisfactory workaround.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Graham Mayor]

I should have explained myself better

What is missing is the updating of the field to reflect the added changes.
This requires the field editing dialog to be accessed and executed, thus the
relevant section should be

With Selection.FormFields(1)
.TextInput.EditType Type:=wdCalculationText, Format:="", _
Enabled:=False
.CalculateOnExit = True
.TextInput.Default = sDefault
'Add the following line to update the field
Dialogs(wdDialogFormFieldOptions).Execute
End With

This would be true even if the bookmark names were used - but the method is
shown in the link to my web page I posted earlier.

However as you say it is now only academic as the OP has employed a
workaround.

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


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