Question to Smarter Folks than I

[1SgtMajor]

My company uses a checklist in MS Word with drop down boxes after each
question with either a "N", "Y", or "NA".

It seems everyone always misses at least one of the drop down boxes which
creates a problem.

Is there some way to create a checklist in "Word" preferably will use
anything right about now...(Smile)..... that the form would NOT be completed
until each box is properly selected with a response?--

Any help would be appreciated.
Steven R.
SgtMajor, USMC (Retired)

 

[Greg Maxey]

SgtMajor,

Is your checklist a Word protected form? If it is and if all of the
checklist dropdown boxes have just three entries "N, Y or N/A," how is one
or more being left blank? Or do you have a "blank entry" set as the defualt
for each question?

If that is the case then you will need to do some validation on field entry
and probably before save or print. This may get you started
http://www.gmayor.com/formfieldmacros.htm
Post back if you need more help.

My company uses a checklist in MS Word with drop down boxes after each
question with either a "N", "Y", or "NA".

It seems everyone always misses at least one of the drop down boxes
which creates a problem.

Is there some way to create a checklist in "Word" preferably will use
anything right about now...(Smile)..... that the form would NOT be
completed until each box is properly selected with a response?--

Any help would be appreciated.
Steven R.
SgtMajor, USMC (Retired)

--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

Arrogance is a weed that grows mostly on a dunghill (Arabic proverb)

 

[Greg Maxey]

SgtMajor,

The code you could you would look something like this:

Option Explicit
Private mstrFF As String

'Use this to set up the checkboxes
Public Sub SetupMacros()
Dim ffItem As Word.FormField
For Each ffItem In ActiveDocument.FormFields
If ffItem.Type = wdFieldFormDropDown Then
With ffItem.DropDown.ListEntries
.Clear
.Add " "
.Add "Y"
.Add "N"
.Add "N/A"
End With
End If
ffItem.EntryMacro = "AOnEntry"
ffItem.ExitMacro = "AOnExit"
Next
End Sub

Public Sub AOnExit()
With GetCurrentFF
If .DropDown.Value = 0 Then
MsgBox "You can't leave field: " & .Name & " blank"
mstrFF = GetCurrentFF.Name
End If
End With
End Sub

Public Sub AOnEntry()
Dim strCurrentFF As String
If LenB(mstrFF) > 0 Then
ActiveDocument.FormFields(mstrFF).Select
mstrFF = vbNullString
End If
End Sub

Private Function GetCurrentFF() As Word.FormField
With Selection
If .FormFields.Count = 1 Then
' CheckBox or DropDown
Set GetCurrentFF = .FormFields(1)
ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
Set GetCurrentFF = ActiveDocument.FormFields _
(.Bookmarks(.Bookmarks.Count).Name)
End If
End With
End Function

You could intercept Print and Save and run this as a final check of all
fields

Sub CheckAll()
Dim ffItem As Word.FormField
For Each ffItem In ActiveDocument.FormFields
If ffItem.Type = wdFieldFormDropDown Then
If ffItem.DropDown.Value = 0 Then
ffItem.Select
MsgBox "You must fill in each ckecklist item."
Exit Sub
End If
End If
Next
End Sub

Post back if you need more help.

My company uses a checklist in MS Word with drop down boxes after each
question with either a "N", "Y", or "NA".

It seems everyone always misses at least one of the drop down boxes
which creates a problem.

Is there some way to create a checklist in "Word" preferably will use
anything right about now...(Smile)..... that the form would NOT be
completed until each box is properly selected with a response?--

Any help would be appreciated.
Steven R.
SgtMajor, USMC (Retired)

--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

Arrogance is a weed that grows mostly on a dunghill (Arabic proverb)

 

[1SgtMajor]

Ahhh...... I should have known a "Shipmate" would come to my rescue...... and
one of my hero's to boot!!! A SubMariner!!!!!........ the one part of my
military service I miss is the shipboard time!! (My neighbor is a WWII
SubMariner..... Great Guy)

Many Thanks for your response, however, I hope I'm not biting off more than
I can chew. (smile)

Let me explain a little further............ I now work for a Financial
Service's Company as a Field Audit Advisor which is pretty much like an IG
team. We visit our assigned offices once a year and conduct an on-site audit
and finish up with a question/answer interview with the Regional VP. The
document we use is a Word document divided into three columns on the sheet.
The L/H column is the question..... center column is our guidance (Standard
or Validation of the question) and the R/H column is the drop down boxes that
contain the "Y", "N" or "N/A" with enough space in the column where we can
type in any additional notes. (The interview is conducted on the lap-top as
we're now a paperless system.)

Many of the questions are a simple "Y" or "N"......such as "Is the
Securities Trade Blotter Properly Maintained"

However, there are questions such as "Is a DP (Designated Principle)
appointed for suitablility reveiw?" "Y" "N" with the next question being.....
If "Yes" is the DP Appointment letter on file? "Y", "N", or "N/A"

Obviously if a DP hasn't been appointed, the follow on question would be
marked "N/A"....... but it's questions like this that will occasionally be
missed and confuses whoever reviews the files as to if a DP was or was not
appointed and if the appropriate paperwork is or is not on file etc.

What I was curious about or my vision on this computer checklist etc........
Why couldn't a checklist be constructed with no answers on the R/H side....
boxes are blank. Conduct the audit etc., however, at the end of the
checklist a button or something that when clicked would not allow you to
finish or close the checklist if a box was not completed. True...... this
would not prevent someone from answering the question wrong....... but would
ensure that each box was reviewed prior to closing the document and the
appropriate "Y", "N" or "N/A" was or was not selected.

From your response.....I believe this concept could be done......perhaps
from someone much smarter than myself (smile) but it does sound fesiable.
I'm returning home from this business trip tomorrow so I'm anxious to start
digging in and seeing if I can come up with a better mousetrap. (smile)

Many Thanks again Greg!!! You've given me something to now ponder. Most of
the folks on the U.S. team are all prior military....so many are not computer
junkies and a few....like me.......know just enough to be dangerous..... but
I know there is so much more that could be done to make this system simplier
and more accurate. I now will have to figure out exactly how to go about
it...... but you've now pointed me into the right direction to start digging.

Best wishes Commander....... And Many Thanks!!

 

[Greg Maxey]

Steven,

Are you and the rest of the former soldiers and patriots on that field audit
team armed? Do you arrive by helicopter or humvee ;-)

Your not biting off more than you can chew. You've got Navy at your back it
always sends somebody else to follow the SEALS.

These dropdown formfiields that you are using have a property ".Enabeld."
You can see this by unprotecting the form, dbl-clicking one of the
Dropdowns, and looking at the field setting "Dropdown enabled." All new
fields are enabled by default. They don't have to be enabled though.

You have a situation where it would be to your tactical advantage if the
field corresponding to the question "Is the DP appointment letter on file?"
was only enabled if the answer to the question "Is a DP appointed for
suitability review?" were yes. And the only correct responses would be
"Yes" or "No"


For this discussion I have bookmarked the formfields "DPAppointed" (the
primary) and "DPAptLtr" (the follow on). When you build your form. Set the
..Enabled property for DPAptLtr to false (uncheck it)

Set the following to run OnExit from DPAppointed:

Sub DPAppointedOnExit()
If ActiveDocument.FormFields("DPAppointed").Result = "Yes" Then
ActiveDocument.FormFields("DPAppLtr").Enabled = True
ActiveDocument.FormFields("DPAppLtr").Range.Select
Else
With ActiveDocument.FormFields("DPAppLtr")
.Enabled = False
.DropDown.Value = 1
End With
End If
End Sub

You should see that when the user answers the primary question and tabs out,
the response will determine if DPAptLtr should be enabled and answered or
skipped.

You could validate the checklist when the user thinks it is finished with
something like this. To keep it simple here I just added a checkbox field
at the very end "chkValidated" and run the macro OnEntry to the checkbox.

Sub CheckAll()
Dim ffItem As Word.FormField
For Each ffItem In ActiveDocument.FormFields
If ffItem.Type = wdFieldFormDropDown And ffItem.Enabled Then
If ffItem.DropDown.Value = 1 Then
ffItem.Select
MsgBox ffItem.Name & " is incomplete. You must fill in each
ckecklist item."
ActiveDocument.FormFields("chkValidated").CheckBox.Value = False
Exit Sub
End If
End If
Next
ActiveDocument.FormFields("chkValidated").CheckBox.Value = True
End Sub

You can get a little more robust and intercept the built-in commands like
Save, SaveAs, Print and run the validations, but if your team members are
anything like the military folks that I am familiar with, they will figure
out a way to foil your best efforts. Personally I recommend you keep it
simple like something shown above and just train your team members to
validate the checklist before printing or submitting. Marines are good at
training right?

Good luck and thanks for your time on the field. Post back if you need more
help.

BTW, I won't post his name and he doesn't live just next door, but my little
corner of western NC and just down the road is also the home of one of the
longest serving Marines in history. He started at the unlawful age of 16 as
an E1 and retired a bird Colonel 43+ years later. Like your neighbor he too
is a great guy.

--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

Arrogance is a weed that grows mostly on a dunghill (Arabic proverb)