Word 2003 VBA to limit options in Inputbox to those in drop down

[johnnykunst]

I have the following macro which works great to make a form drop down list mandatory to fill in

Sub MustFillIn(
If ActiveDocument.FormFields("nameDD").Result = "ENTER NAME" The
D
sInFld = InputBox("This field must be filled in, fill in below."
Loop While sInFld = "
ActiveDocument.FormFields("nameDD").Result = sInFl
End I
End Su

However, the user is free to enter whatever they want in the input box, although the field this fires from if nothing is entered is a drop down- is there any way to make the input box that pops up contain the same list as the actual drop down on the form

 

[Graham Mayor]

You can force the user to fill in the field by using a macro on exit from
the field and another on entry to any other field the user might select. The
macros are aonexit and aonentry and the principles are covered at
http://www.gmayor.com/formfieldmacros.htm

Option Explicit
Public rngFF As Word.Range
Public fldFF As Word.FormField
Public mstrFF As String
Public Sub AOnExit()
With GetCurrentFF
If .Type = wdFieldFormDropDown Then
If .Result = "ENTER NAME" Then
MsgBox "You must make a selection""
End If
End If
mstrFF = GetCurrentFF.name
End With
End Sub
Public Sub AOnEntry()
If ActiveDocument.FormFields(mstrFF).Result = "ENTER NAME" Then
ActiveDocument.FormFields(mstrFF).Select
mstrFF = vbNullString
End If
End Sub
Public Function GetCurrentFF() As Word.FormField
Set rngFF = Selection.Range
rngFF.Expand wdParagraph
For Each fldFF In rngFF.FormFields
Set GetCurrentFF = fldFF
Exit For
Next
End Function


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[johnnykunst]

The trouble is the user may not even enter or exit the form field at all, in which case that macro wouldn't work. The macro I posted is inserted into a different macro I have which automates the save process, and is linked to specified fields- it works fine for text entries, but for drop downs, because the input box is free text, the user can input anything they want, rather than only the actual items in the drop down, so what I want to do is have the input box only give the drop downs as possible inputs.

 

[johnnykunst]

So basically, I need a macro that will check a specified drop down entry and if it contains "ENTER NAME" it will pop up an input box with just the options on the original drop down available to be input & this would not be dependant on the user clicking in or out of the field.

 

[Graham Mayor]

If you apply the AOnEntry macro to all the fields and the AOnExit macro to
the dropdown field, the user will not be able to complete the form until a
selection is made from the dropdown. The following macro will apply the
AOnEntry macro to all the fields

Public Sub SetupMacros()
Dim ffItem As Word.FormField
For Each ffItem In ActiveDocument.FormFields
ffItem.EntryMacro = "AOnEntry"
Next
End Sub

Even better, get rid of the dropdown field altogether and setup a userform
to get the required user input and send the result to a docvariable which
you can display in the document with a docvariable field - or send it to a
text form field.

In the case of the latter consider a userform with a list box, a label and a
command button and on the document a text form field to take the result. The
example uses default names, but you can (and should) change those. Call the
userform from an AutoNew macro in the form template eg
Sub AutoNew()
UserForm1.Show
End Sub

and the userform will display when a new form is created from the template.
The form won't let the user cancel, or proceed until a value is entered in
the list box.

The form code could be

Option Explicit
Private Sub UserForm_Initialize()
Me.Caption = "Select name & click Next"
Me.CommandButton1.Caption = "Next"
With Me.Label1
.Caption = ""
.ForeColor = wdColorRed
.TextAlign = fmTextAlignCenter
.Font.Size = "14"
End With
With Me.ListBox1
.Clear
.AddItem " "
.AddItem "Fred"
.AddItem "Bill"
.AddItem "John"
.AddItem "Susan"
.ListIndex = 0
End With
End Sub
Private Sub CommandButton1_Click()
If Me.ListBox1.Value = " " Then
Me.Label1.Caption = "You must select a name!"
UserForm1.Repaint
Exit Sub
End If
ActiveDocument.FormFields("Text1").Result = Me.ListBox1.Value
Unload Me
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
Cancel = True
CommandButton1_Click
End If
End Sub



--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[johnnykunst]

That's great, but the problem is that for the first, it is only certain fields that are mandatory
For the second option, I couldn't use as I need to keep the drop downs as others will be editing the form from time to time & would not be able to use VBA
Sorry!

 

[Graham Mayor]

If the users still have the template then they could run the macro again. If
you have more than one mandatory dropdown field, simply add another list box
(or boxes) to the userform to collect the date for the field(s) in question
and process them in the same way. It would only take a few more lines of
code to read back any existing values from the fields as defaults for the
re-displayed userform list boxes. e.g.

With Me.ListBox1
.Clear
.AddItem " "
.AddItem "Fred"
.AddItem "Bill"
.AddItem "John"
.AddItem "Susan"
.ListIndex = 0
For i = 1 To .ListCount - 1 'skip the first item (0)
If ActiveDocument.FormFields("Text1").Result = _
.list(i) Then
.ListIndex = i
End If
Next i
End With

Furthermore, rather than edit the form from time to time, wouldn't it be
better to create a new form?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>