Re: Form Protection with Excel Object Embedded

Hi Debbie,

You can simply select the field from the tutorial, copy & paste it into your document. Then, select it there, press Shift-F9 to expose the field code and make the changes indicated in my previous post. When you're done, protect your document and you're on your way.

--
Cheers
macropod
[Microsoft MVP - Word]


"Debbie" <Debbie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:E29007BD-077C-4220-A2CA-A93DDC13A89C@xxxxxxxxxxxxxxxx
Hi Macropod,

First, let me apologize for not responding earlier. I have been trying to
figure out some basic items, and I really didn't want to bother you after you
provided me such wonderful detailed items. I really thank you for your
efforts.

Unfortunately, I am stuck on seemingly the easiest thing. First, it took me
a while to figure out not to enter this information as a text form field!
After much looking, I determined I needed to use Insert-Field. But now, am I
supposed to use Formula, or Date, or what? I thought I couldn't copy the "{"
into the formula, but I was able to, so am I doing something wrong (needing
to use CTRL-F9)? In other words, what is the exact procedure to get this
complex formula in a field???

I feel pretty bad about responding so late, but even more so that I couldn't
figure out where and how to input this information!

Thanks again!
Debbie


"macropod" wrote:

Hi Debbie,

For your purposes, the best field to base the calculation on might be the one in the example titled 'Calculate the # Days Difference
Between Two Dates'. Only a few small modifications would be needed:
1. Replace the first manual line-break with a left bracket '('
2. Replace the last manual line-break with ')/365.2425'
3. Change the final ',0' to '0.0' or '0.00' or '0.000' (depending on the desired precision).
The finished field will look like:
{=({SET a{=INT((14-{EndDate \@ M})/12)}}
{SET b{={EndDate \@ yyyy}+4800-a}}
{SET c{={EndDate \@ M}+12*a-3}}
{SET d{EndDate \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045}
-
{SET a{=INT((14-{StartDate \@ M})/12)}}
{SET b{={StartDate \@ yyyy}+4800-a}}
{SET c{={StartDate \@ M}+12*a-3}}
{SET d{StartDate \@ d}}
{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045})/365.2425 \# 0.000}

Then all you need to do is give the formfield used for the birthdate the 'StartDate' bookmark name and the formfield used for the
other inputdate the 'EndDate' bookmark name and set both formfields' properties to 'calculate on exit'.

The date input format is of no consequence, provided both dates have a day, month and year- you might want to enforce this via the
formfield properties.

As for the Section breaks, yes, they work on a whole-of-line basis. You should be able to insert one immediately before & after the
Excel workbook, though. At most, you'll need to insert a paragraph break immediately before the workbook, then format the workbook
as in-line with text while you're inserting the Section breaks - to make sure the workbook doesn't end up being anchored to a
protected Section.

--
Cheers
macropod
[Microsoft MVP - Word]


"Debbie" <Debbie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:DC8F67D9-290F-44CC-94D5-B31BBBCE0E66@xxxxxxxxxxxxxxxx
> Thank you, Macropod. I downloaded your document and read it head-to-toe (and
> it was excellent!) It appears that one of the examples is close to what I
> would like, but not exactly.
>
> Basically, what I am trying to do is to calculate the #Years & #Months
> between a birthdate and an inputted date, and have it give me an answer in
> decimal format. For example, if someone is 12 years 6 months old (regardless
> of # of days), I want it to say 12.5 years (.5 equalling 6 months). So 12
> years and 8 months would be 12.6 (rounded down or up is fine). I don't need
> absolute exactness.
>
> I don't know whether to use a version of "Interactively Calculating a
> Person's Age" (it will not be interactive, but input on a form), or
> "Calculate the # Years Months & Days Difference Between Two Dates".
>
> I am unsure as to the format of the input date as well. Does it need to be
> d M yyyy or is mm-dd-yyyy or another other format okay? If I accept another
> format, do I need to first reformat it to be accepted in the calculation?
>
> One more thing - about section breaks in case the above doesn't work in the
> end. It appears when I put a section break right after a field in a Word
> document, it automatically extends to the end of the line and forces the next
> field (or text) to the next line. I am using
> Insert->Break->Section->Continuous. Based on what you are saying, that
> should not happen - I should be able to "wrap" protected areas around
> unprotected areas. How do you do this?
>
> Thanks so much for your time!
> -- > Debbie
>
>
> "macropod" wrote:
>
>> Hi Debbie,
>>
>> Word can do date calculations! To see how to do just about everything else you might want to do with dates in Word, check out >> my
>> Word Date Calculation Tutorial, at:
>> http://www.wopr.com/index.php?showtopic=249902
>> or
>> http://www.gmayor.com/downloads.htm#Third_party
>> Do read the document's introductory material.
>>
>> If you prefer to retain your Excel workbook, though, insert Section breaks either side of it and leave that Section >> unprotected
>> when
>> you apply the forms protection.
>>
>> -- >> Cheers
>> macropod
>> [Microsoft MVP - Word]
>>
>>
>> "Debbie" <Debbie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:564423A9-7C8B-4146-8323-D46033BDF49E@xxxxxxxxxxxxxxxx
>> > Hi,
>> >
>> > I am using Office XP (Word). I created a form which embeds an excel object
>> > file.
>> >
>> > When it is unprotected, I am able to double-click the excel file change a
>> > cell, and another cell recalculates appropriately.
>> >
>> > When I protect the document, I am not able to access the excel object at all
>> > to change it - it is skipped right over when tabbed to.
>> >
>> > The excel recalculation cell goes beyond the normal arithmetic functions of
>> > the Word Formulas in Tables, so I can't use those (it is a date calculation).
>> >
>> > The only way around it so far that I see is to separate the form into
>> > sections, where the excel table is on its own line, unprotected in its own
>> > section. Is there a way to do it without unprotecting the line?
>> >
>> > Thanks in advance for all your help!
>> > -- >> > Debbie
>>
>>



.

Relevant Pages

  • Re: Form Protection with Excel Object Embedded
    ... "macropod" wrote: ... the best field to base the calculation on might be the one in the example titled 'Calculate the # Days ... The date input format is of no consequence, provided both dates have a day, month and year- you might want to enforce this via ... If you prefer to retain your Excel workbook, though, insert Section breaks either side of it and leave that Section ...
    (microsoft.public.word.application.errors)
  • Re: Formatting XML in Excel?
    ... A older xls workbook in 2007 needs to save the workbook as xlsm before the macrowill work. ... Another choice is the share option in access which automatically converts the data to excel may be quicker. ... The company does like the format. ...
    (microsoft.public.excel.misc)
  • Re: Form Protection with Excel Object Embedded
    ... the best field to base the calculation on might be the one in the example titled 'Calculate the # Days Difference ... The date input format is of no consequence, provided both dates have a day, month and year- you might want to enforce this via the ... If you prefer to retain your Excel workbook, though, insert Section breaks either side of it and leave that Section unprotected ... Is there a way to do it without unprotecting the line? ...
    (microsoft.public.word.application.errors)
  • Re: Performance on calculation - 2003 vs 2007
    ... Means finding out which formulae in the workbook are using calculation ... "prioritising the parts of the workbook that are using up the calculation ... memory 1GB under Office 2003? ... Excel working when they are hit. ...
    (microsoft.public.excel.misc)
  • Re: Manual Calculation in Excel 2000
    ... I'm still betting that you have another workbook that opens and tells excel how ... I'd look to see what other files are opened when excel starts. ... the manual calculation setting stays as is until I change it. ...
    (microsoft.public.excel.misc)