Drop down form field- filling large list without client-side VBA?

[ker_01]

I'm helping a colleague who would like to add a few drop-downs on an existing
form, each needing ~300 items. I'm looking for a way to avoid the tedious
task of putting them (individually) in the drop-down form field options
dialogue.

In Excel I'd just put the list on a hidden sheet, or load an activeX
dropdown control (on the fly) with VBA... but this form will be distributed,
and we can't count on end users enabling macros...

So at this point, I'm just looking for a fast way to load the items- is
there perhaps a way to load the form field once via VBA and have it retain
those settings when saved, even when the VBA code is subsequently removed?

I appreciate any direction or advice.
Thank you!
Keith

 

[Doug Robbins - Word MVP]

To start with, the list in a drop down formfield is limited to 25 items. If
you have more than that, you need to use a work around that actually
displays a userform:

http://support.microsoft.com/kb/198561

You should then consider whether it was not a userform that should be being
used in the first place.

See the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

and the following pages of fellow MVP Greg Maxey's website :

http://gregmaxey.mvps.org/Create_and_employ_a_UserForm.htm

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

If that information is of use to you, please do consider contributing to the
maintenance of that website to ensure its continued availability.

For dropdown formfields with no more than 25 items, you could replace the

InputBox("Enter the items for the list, separating each one with a |.",
BMName & " DropDown List Items")

part of the following macro with the string that contains the items, or you
could write code to access a list from some other location:

Sub InsertDropDownFormField()
Dim BMName As String
Dim ddList As Variant
Dim ddff As FormField
Dim i As Long
BMName = "dd" & InputBox("Enter the Identifier for the formfield", "DropDown
FormField")
If ActiveDocument.Bookmarks.Exists(BMName) Then
MsgBox "A dropdown formfield with that bookmark name " & BMName & "
already exists in the form."
Exit Sub
Else
ddList = Split(InputBox("Enter the items for the list, separating each
one with a |.", BMName & " DropDown List Items"), "|")
Set ddff = ActiveDocument.FormFields.Add(Selection.Range,
wdFieldFormDropDown)
With ddff
.Name = BMName
For i = 0 To UBound(ddList)
.DropDown.ListEntries.Add ddList(i)
Next i
End With
End If

End Sub





--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[ker_01]

Doug-

Thank you for your reply. I've played mostly in the Excel sandbox, so the
forms controls are new to me- I hadn't realized there was a 25-item limit.
I'll give my colleague the choice of either dropping the list altogether, or
accepting the need for VBA. If VBA is acceptable, I'll be right at home
making a userform.

Best,
Keith