clicking in protected forms: MS knowledge base article ID: 839950

[James III]

Hello,
I inherited a protected template that has a really obnoxious idiosyncrasy
related to form fields; I found the idiosyncrasy listed in the MS knowledge
base as article ID: 839950.

In summary, the user must click or tab from one form field into another form
field on a protected document or Word does not run the form field's 'on
entry' and 'on exit' macros. Note that in this template the 'on exit' macro
does data validation and therefore when the user clicks into some other area
of the document the data is not validated at that time (this template uses
text and checkbox form fields).

Although another form field in the template calls a macro that identifies
form fields that have not been validated properly, overall this comes across
as a poor design and depending on the user, can be very frustrating/confusing.

It has been suggested that we use some type of timer to trigger a validation
of the data before the user removes the focus. However, that idea seems to
be insufficient, because how do we trigger the timer if the 'on entry' macro
was bypassed. Also, it would be tricky to know how long to wait before
forcing the data to be validated (assuming that it could even be done, since
the user has not necessarily removed focus from the control).

After that suggestion, I started thinking about keeping track of the
"current" form field with an index counter and validating the data if the
counter does not match the selected form field's name (the names all have an
imbedded index that can be identified for processing). This idea seems
limited becuase if the user leaves several form fields "improperly" how would
I know which ones to validate.

I've also been looking for some way to capture a click event in the
protected non-form field areas, but apparently the click events are always
tied to a form field.

An obtuse 'solution' that I proposed is to include in the template an
explanation of how to enter/exit the form fields 'properly' (obviously not
the desired path).

Any suggestions would be greatly appreciated!

 

[Jay Freedman]

Hi James,

Ridiculous garbage like this is why I try to avoid protected forms
altogether. A much better mechanism is to create a UserForm (custom dialog)
to gather all the information. The controls in UserForms allow both
immediate and delayed validation -- while the user is still typing, or when
they go to another control by any means, or when they click the OK button to
dismiss the dialog. You also have a wider selection of controls, such as
spinners and a date picker.

A quick intro is at
http://www.word.mvps.org/FAQs/Userforms/CreateAUserForm.htm. Instead of
using bookmarks as done there, though, I'd recommend putting the results
from the UserForm into document variables and displaying them with
DocVariable fields in the document. That also makes it simpler for the
UserForm's Initialize procedure to pick up existing values to display if the
form is run again for editing.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[James III]

Hi Jay,
Thank you for your response. I tried the userform and I can see why you use
it.

However, the "protected document" that I am dealing with is actually 16
different protected documents. And each is a Test Procedure (for one of our
product's sub-assemblies) that has numerous steps; each step has either a
single checkbox form field or several text form fields for min and max values
and measured data entry in addition to the checkbox form field.

In other words, it appears to me that it would be a significant rework to
convert the all of the steps with text form fields so the data is entered via
a userform. However, unless I can find a slick & easy solution for now, this
may be the only alternative. I can definately keep this in mind for the
future.

Note that in trying out the userform, I used a command button on the
document to populate controls on the userform with data from the document and
then to show the userform; then I used a command button on the userform to
copy the entered data back into the document and then to hide the userform.
However, the code I used is in the ...button_click() subroutine. Since I
will have numerous steps, it will require a _click() subroutine for each
step. Is there anyway to trigger on a mouse click without the associated
code being tied to a specific control?

One other question: the
http://www.word.mvps.org/FAQs/Userforms/CreateAUserForm.htm
had me create an 'autonew' macro. Does that name mean anything special?

Again, Thank you for your time and assistance!

 

[fumei via OfficeKB.com]

"had me create an 'autonew' macro. Does that name mean anything special?"

Indeed it does. It means exactly what is stated on that page:

"When you create a new document from the template, the autonew macro opens
the user form for the user to input data"

AutoNew - or more currently, Document_New - will display the userform when a
new document is cloned from the template. You have not stated if this is, in
fact, what you are doing.

You can also have a userform display whenever a document is opened (as
opposed to a new document being cloned from a template), using the
Document_Open event.

"I used a command button on the document to populate controls on the userform
with data from the document "

One way, but you could also use the Userform_Initialize event to do this,
with something else (a keyboard shortcut, a button, a menu item) that will
fire Userform.Show.

“An obtuse 'solution' that I proposed is to include in the template an
explanation of how to enter/exit the form fields 'properly' (obviously not
the desired path).â€

I am afraid I am going to have to disagree. Users using a document properly
IS the desired path.

That being stated, data validation (as well as top notch error trapping) is
the hardest thing of all to do.

Overall, while I do use formfields, and I do think there are valid uses for
them, I am with Jay on this. DocVariable fields are very handy….except for
the ease of user editing of the values. DocVariables are great for putting
stuff IN, but not very good for getting stuff from the users. On the other
hand, userforms ARE good at getting stuff from users. Far better, IMO, than
formfields. Because you CAN do solid data validation/error trapping much
much easier. So between the two (userform input and DocVariable fields) you
can keep a good grip on the document.

You could put a button on a toolbar (or a shortcut key) that displays the
userform. If there ARE values already existing, the userform fills its
fields with those values. Its OK button – post data validation, or
performing data validation – writes those values.

User wants to change things…they click the button and get the userform again.
In other words, ALL input is done through the userform.

“it appears to me that it would be a significant rework to convert the all of
the steps with text form fields so the data is entered via a userform.â€

Yes, it probably would be. However, once done, it would be a more robust
process.

Hi Jay,
Thank you for your response. I tried the userform and I can see why you use
it.

However, the "protected document" that I am dealing with is actually 16
different protected documents. And each is a Test Procedure (for one of our
product's sub-assemblies) that has numerous steps; each step has either a
single checkbox form field or several text form fields for min and max values
and measured data entry in addition to the checkbox form field.

In other words, it appears to me that it would be a significant rework to
convert the all of the steps with text form fields so the data is entered via
a userform. However, unless I can find a slick & easy solution for now, this
may be the only alternative. I can definately keep this in mind for the
future.

Note that in trying out the userform, I used a command button on the
document to populate controls on the userform with data from the document and
then to show the userform; then I used a command button on the userform to
copy the entered data back into the document and then to hide the userform.
However, the code I used is in the ...button_click() subroutine. Since I
will have numerous steps, it will require a _click() subroutine for each
step. Is there anyway to trigger on a mouse click without the associated
code being tied to a specific control?

One other question: the
http://www.word.mvps.org/FAQs/Userforms/CreateAUserForm.htm
had me create an 'autonew' macro. Does that name mean anything special?

Again, Thank you for your time and assistance!

Hi James,

[quoted text clipped - 56 lines]

 

[Jay Freedman]

I agree with all of fumei's points. I'll just take on the one section that
wasn't addressed yet:

I'm not quite clear on what constitutes a step, but in general you have to
provide an event procedure for every event you want to respond to. That may
be a button click, a character typed or erased in a text box (the "change"
event of that control), entry into or exit from a control, etc. So the
answer to your question is "no".

If you provide helper functions in the userform code, then a lot of these
event procedures will consist of only one or two lines of code that call a
helper function with the proper arguments and then set a value or branch on
the result of the function. An example of a helper function might be one
that takes a value and determines whether it's valid for the current
control, returning a Boolean value (similar to the built-in IsNumeric
function). In addition to the value itself, you can pass limits and other
criteria as arguments.

A lot of these little event procedures can be "written" by copy/paste/small
edit. Also, you can use the two dropdowns at the top of the VBA editor
window for speed: select the control from the left dropdown, select the
event from the right dropdown, and VBA will create the event procedure's
first and last lines for you.

You should also look into the Multipage control, which lets you create more
than one page in a userform and use Back and Next buttons to control
navigation.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

[quoted text clipped - lots of lines]

Any suggestions would be greatly appreciated!

 

[James III]

fumei & Jay,
Thank you for your assistance. It's feels good to not be alone in this
Microsoft Jungle!!!
--
James III,
Sincere VBA Newbie!

I agree with all of fumei's points. I'll just take on the one section that
wasn't addressed yet:

I'm not quite clear on what constitutes a step, but in general you have to
provide an event procedure for every event you want to respond to. That may
be a button click, a character typed or erased in a text box (the "change"
event of that control), entry into or exit from a control, etc. So the
answer to your question is "no".

If you provide helper functions in the userform code, then a lot of these
event procedures will consist of only one or two lines of code that call a
helper function with the proper arguments and then set a value or branch on
the result of the function. An example of a helper function might be one
that takes a value and determines whether it's valid for the current
control, returning a Boolean value (similar to the built-in IsNumeric
function). In addition to the value itself, you can pass limits and other
criteria as arguments.

A lot of these little event procedures can be "written" by copy/paste/small
edit. Also, you can use the two dropdowns at the top of the VBA editor
window for speed: select the control from the left dropdown, select the
event from the right dropdown, and VBA will create the event procedure's
first and last lines for you.

You should also look into the Multipage control, which lets you create more
than one page in a userform and use Back and Next buttons to control
navigation.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

[quoted text clipped - lots of lines]

Any suggestions would be greatly appreciated!