Auto Calculation with Field Duplication

[kW in mD]

Hi,
I have developed a document for a client in which 13 rows of fields with 3
columns each auto-calculate into 3 separate totals. The client is now
requesting that, if they enter zero into the first column of any row, the
other 2 columns are immediately updated to be zero as well. However, this
should only happen with the number zero. How can I program the fields to do
this and still auto-calculate?

Karen

 

[Jean-Guy Marcil]

Hi,
I have developed a document for a client in which 13 rows of fields with 3
columns each auto-calculate into 3 separate totals. The client is now
requesting that, if they enter zero into the first column of any row, the
other 2 columns are immediately updated to be zero as well. However, this
should only happen with the number zero. How can I program the fields to do
this and still auto-calculate?

Just about to leave for the weekend... just had an idea.. don't know if it
will work...

The probklem you have is that a field is either user-fillable or it is
automatic(Formfield vs Formula). You can't really have both.

One idea I have is that you have a macro On Exit for the fields in the first
column of each row. First make sure that all fields have a default value of 0.
The macro would check if the field is equal to 0, if it is, set all other
fields on the same row to their default value (0) and disable them. If it is
not 0, enable the fields on the same row.

 

[kW in mD]

Just about to leave for the weekend... just had an idea.. don't know if it
will work...

The probklem you have is that a field is either user-fillable or it is
automatic(Formfield vs Formula). You can't really have both.

One idea I have is that you have a macro On Exit for the fields in the first
column of each row. First make sure that all fields have a default value of 0.
The macro would check if the field is equal to 0, if it is, set all other
fields on the same row to their default value (0) and disable them. If it is
not 0, enable the fields on the same row.

Hi,
Thank you for your suggestion. Would this look like an "IF" calculation? I'm
fairly new to VBA scripting; how would such a code best be written so that if
Score1 = 0, then Score2 and Score3 also = 0, but if Score1>0, Score2 and
Score3 are whatever score they are?

Karen

 

[Jean-Guy Marcil]

Hi,
Thank you for your suggestion. Would this look like an "IF" calculation? I'm
fairly new to VBA scripting; how would such a code best be written so that if
Score1 = 0, then Score2 and Score3 also = 0, but if Score1>0, Score2 and
Score3 are whatever score they are?

To test my macro, set up a table as follows:

5 columns
Columns 2, 3 and 4 contain textinput formfields of the "Number" type with a
default value set to "0". Also, check the "Calculate on Exit" property.
The fields in column 2 also have the following macro (ToggleZero) set On Exit.
Column 5 contains a calculation formfield witht he following formula:
=SUM(LEFT)
Make sure that each cells contains nothing but one single formfield as
described above.
Do not forget to protect the document before testing it.

Now, here is the macro:

Sub ToggleZero()

With Selection
If .Cells(1).Range.FormFields(1).Result = 0 Then
With .Rows(1).Cells(3).Range.FormFields(1)
.Result = .TextInput.Default
.Enabled = False
End With
With .Rows(1).Cells(4).Range.FormFields(1)
.Result = .TextInput.Default
.Enabled = False
End With
Else
With .Rows(1).Cells(3).Range.FormFields(1)
.Enabled = True
End With
With .Rows(1).Cells(4).Range.FormFields(1)
.Enabled = True
End With

End If
End With

End Sub

 

[kW in mD]

To test my macro, set up a table as follows:

5 columns
Columns 2, 3 and 4 contain textinput formfields of the "Number" type with a
default value set to "0". Also, check the "Calculate on Exit" property.
The fields in column 2 also have the following macro (ToggleZero) set On Exit.
Column 5 contains a calculation formfield witht he following formula:
=SUM(LEFT)
Make sure that each cells contains nothing but one single formfield as
described above.
Do not forget to protect the document before testing it.

Now, here is the macro:

Sub ToggleZero()

With Selection
If .Cells(1).Range.FormFields(1).Result = 0 Then
With .Rows(1).Cells(3).Range.FormFields(1)
.Result = .TextInput.Default
.Enabled = False
End With
With .Rows(1).Cells(4).Range.FormFields(1)
.Result = .TextInput.Default
.Enabled = False
End With
Else
With .Rows(1).Cells(3).Range.FormFields(1)
.Enabled = True
End With
With .Rows(1).Cells(4).Range.FormFields(1)
.Enabled = True
End With

End If
End With

End Sub

This worked beautifully! Thank you very much for your help.

Karen