|
|
The information in this website is provided without risk or
obligation and free of charge. However, if you have benefitted
from my efforts here and would like to make a contribution to
help me continue and maintain this work then any donation will
be greatly appreciated. Please click the adjacent button to
access PayPal. Thank you.
|
|
This Microsoft Word Help & Tips page will show you
you an some advanced methods to use FormField textbox fields that overcome the
25 item constraint in FormField dropdown fields. The limitation in
Word forms caused by this constraint is fully illustrated in my
Linked DropDown Fields.
The method shown here is an adaptation of the basic method shown in Microsoft KB Article
19856.
As an example, let's assume we have a simple
online protected form in with the user is asked to pick a color, a
letter from the alphabet, a number, a name, and their U.S. state of
residence. Each of these selections will come from a list of 26 or
more items. A FormField dropdown field won't work because of the
25 item limit. |
|
|
|
Each of the FormFields are standard text fields.
The fields are bookmarked "Color," "Letter," etc. and each of
the fields are configured to run a macro on entry and on exit to the field. The options for the "Color"
field are illustrated below: |
|
|
|
The Entry macro will create a UserForm combobox or listbox
(your choice) populated with a list of colors for the user to choose when the user
tabs into or selects the field. The following
illustrations show a combobox used for the "Color" field and a listbox
used for the "State" field. I suppose that both have
their advantages so the choice of which to use is really up to you. |
|
|
|
|
Since the text field is "Fill-in enabled" (it must
enabled or the user could not enter the field and fire the Entry macro)
it is possible that the user could dismiss the UserForm and put their own
text in the field. If you wanted to prevent this you can use an Exit macro to ensure
the text field entry matches an item on the defined list. The user
is informed of a invalid entry via a separate msgbox (shown on the
right below) and then returned to
the UserForm to make a valid selection. If you elect to use a
combobox and retrict entries to the items on the list you will need to
set the "MatchRequried" property of the combobox = True. If a user
enters an invalid entry in the combobox textfield a notification is
generated as shown on the left. |
|
|
|
|
Below I will show you some representative code
to create UserForm listboxes. This method uses a single UserForm and VBA code
that determines the current FormField and populates the UserForm listbox
(with list items hard coded in the VBA) accordingly. The standard Project Module and UserForm Module code
is shown below: |
Put the following code in a
Standard Project Module:
Public oFldName As String 'Public declaration
makes this data available to the UserForm
Public listArray() As String
Private mstrFF As String
Private i As Long
Sub OnEntryBuildDD_HC()
Dim myFrm As UserForm1
'This is part of the validation process.
"mstrFF" stores the name of the formfield with an invalid entry
If LenB(mstrFF) > 0 Then
ActiveDocument.FormFields(mstrFF).Select
mstrFF = vbNullString
End If
On Error GoTo Err_Handler
'Call a Function to determine the name of the
current formfield
oFldName = GetCurrentFF.Name
'Call a Function to determing the list items for
display
listArray = GetArray(oFldName)
'Display the UserForm
Set myFrm = New UserForm1
myFrm.Show
Exit Sub
Err_Handler:
MsgBox Err.Number & " " & Err.Decription
End Sub
Private Function GetCurrentFF() As Word.FormField
With Selection
If .FormFields.Count = 1 Then
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
Function GetArray(ByRef pName As String) As Variant
Select Case pName
Case Is = "Color"
GetArray =
Split("Red,Blue,Green,White,Orange,Yellow,Teal,Lavender,Peach,Brown," _
& "Purple,Black,Light Blue,Light
Yellow,Light Green,Silver," _
& "Gold,Grey, 10% Grey, 15% Grey, 20%
Grey, 25% Grey", ",")
Case Is = "Letter"
GetArray =
Split("A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z", ",")
Case Is = "Numbers"
'Use
addtional Case Is and GetArray statements for the remaining formfields.
End Select
End Function
Sub OnExitCustDD_HC()
Dim myFrm As UserForm1
Dim bValidate As Boolean
bValidate = False
With GetCurrentFF
If .Result = "" Then Exit Sub
'Call Function to get the list
items
listArray = GetArray(.Name)
'Compare user entry to list
items. Exit when match is found
For i = LBound(listArray) To UBound(listArray)
If .Result = listArray(i) Then
bValidate =
True
Exit For
End If
Next i
'If not found (user typed
invalid data in the field) then alert user, clear field data.
If Not bValidate Then
MsgBox "Your made an invalid entry."
& vbCr & vbCr & " Please choose an item from the list.", _
vbInformation + vbOKOnly, "Invalid Entry"
mstrFF = .Name
.Result = ""
End If
End With
End Sub
UserForm Code:
Sub UserForm_Initialize()
Dim i As Long
Dim pStr As String
Dim lngLeft As Long, lngTop As Long, lngWidth As Long, lngHeight As Long
Select Case oFldName
Case Is = "Color"
Me.Caption = "Colors"
'You can
specify and custom label caption
Me.Label1.Caption = "DblClk to
select"
'You can
sort the list items
WordBasic.SortArray listArray, 0
ListBox1.List = listArray
Case Is = "Letter"
Me.Caption = "Letters"
Me.Label1.Caption = "DblClk to
select"
WordBasic.SortArray listArray, 0
Case "Number"
'Use
case statesments as shown above for remain fields.
End Select
'Populate the listbox
ListBox1.List = listArray
'Display the UserForm adjacent to the formfield.
Me.StartupPosition = 0
ActiveWindow.GetPoint lngLeft, lngTop, lngWidth, lngHeight, Selection.Range
Me.Top = lngTop / 1.33 - 100
Me.Left = lngLeft / 1.33
'Set the value of the listbox list item to match
previous user selection
For i = 0 To ListBox1.ListCount - 1
If ListBox1.List(i) =
ActiveDocument.FormFields(oFldName).Result Then
ListBox1.ListIndex = i
Exit For
End If
Next i
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Select Case oFldName
Case Is = "Color"
ActiveDocument.Range.FormFields("Color").Result = Me.ListBox1.Text
Case Is = "Letter"
ActiveDocument.Range.FormFields("Letter").Result = Me.ListBox1.Text
Case "Number"
'Use
case statesments as shown above for remain fields.
End Select
'Kill the UserForm
Unload Me
End Sub |
|
All you need to do to add additional textbox
fields that will funtion as dropdown fields is to create the fields with
the appropriate bookmark name and add additional Case statements to the
procedures shown above. There is no need to hard
code the list entries in the VBA procedure as illustrated here.
The list items can be stored in external source for ease of maintenance.
I have attached a .zip file with a Word template and an Access data
base. The template contains code simliar to the code shown here,
the code for creating combobox lists and the code
necessary to extract the list items from the Access data base.
Click here to download: File Package
Good Luck!! |
 Bonus
Tip: Need help with creating a UserForm? See
my Custom VBA Message Box tips page |
|
|
Looking for something else?
|
|
|
|
|
|
