Form Protection with Excel Object Embedded

[Debbie]

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!

 

[macropod]

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.

 

[Debbie]

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

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]

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!

 

[macropod]

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]

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

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]

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]

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

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]

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

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]


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!

 

[macropod]

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]

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

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]

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


:

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]


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]

Woo-hoo! Finally! It now works, and not only that, I was able to do other
mods to the formula as well. They are so cool, and they flow so nicely in
the form! Thanks so much for your help. I may be emailing again for another
purpose, as these are pretty complex formulas.

Thanks again!
--
Debbie

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]

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

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]


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


:

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]


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!