Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / March 2008

Tip: Looking for answers? Try searching our database.

Auto Calculation with Field Duplication

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
kW in mD - 20 Mar 2008 20:37 GMT
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 - 20 Mar 2008 21:04 GMT
> Hi,
> I have developed a document for a client in which 13 rows of fields with 3
[quoted text clipped - 3 lines]
> 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 - 25 Mar 2008 16:51 GMT
> Just about to leave for the weekend... just had an idea.. don't know if it
> will work...
[quoted text clipped - 7 lines]
> 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 - 25 Mar 2008 17:42 GMT
> > Just about to leave for the weekend... just had an idea.. don't know if it
> > will work...
[quoted text clipped - 13 lines]
> 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 - 27 Mar 2008 13:50 GMT
> > > Just about to leave for the weekend... just had an idea.. don't know if it
> > > will work...
[quoted text clipped - 54 lines]
>
> This worked beautifully! Thank you very much for your help.

Karen

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.