Word

Dropdown Form Field 25 limit list workaround

Ease of Use

Intermediate

Version tested with

2002 

Submitted by:

OTWarrior

Description:

A way of having more than 25 items in a drop down form field list by clearing the list and add different values. Requires macros 

Discussion:

The Dropdown Form Field is a great option to use on computers with few system resources, however there are some minor issues, the main one being that you can only have 25 options on your list. Since this is not changable in any way, the way around is to clear the current items held in the list, and repopulate the list with other values (held in an array) which are hidden in the macro. 

Code:

instructions for use

			

Public BKMName As String Public ResultCheck As String Public nGenCount As Boolean Public bprotected As Boolean Public unHighlighted As Boolean Public ffSelect As Word.FormField Public ffSelectList As Word.ListEntries Public ffSelectDropdown As Word.DropDown Public HighlightSettings As Word.Range Option Explicit Public Sub AOnEntry() Set HighlightSettings = Selection.Range If Selection.FormFields.Count = 1 Then 'No textbox but a check- or listbox BKMName = Selection.FormFields(1).Name ResultCheck = ActiveDocument.FormFields(BKMName).Result Call ffGenerator Exit Sub ElseIf Selection.FormFields.Count = 0 And Selection.Bookmarks.Count > 0 Then BKMName = Selection.Bookmarks(Selection.Bookmarks.Count).Name ResultCheck = ActiveDocument.FormFields(BKMName).Result Call ffGenerator Exit Sub Else MsgBox "TOASTIE!!" End If ResultCheck = ffSelect.Result Call ffGenerator Exit Sub End Sub Public Function ffGenerator() On Error GoTo ErrerHandel Set ffSelect = ActiveDocument.FormFields(BKMName) Set ffSelectDropdown = ffSelect.DropDown Set ffSelectList = ffSelectDropdown.ListEntries nGenCount = False Dim Data As Variant Data = Array("This", "Works", "Rather", "Well", "Wouldn't", "You", "Say?", "..Next List...") Dim Data2 As Variant Data2 = Array("Data1", "Data2", "Data3", "Data4", "...Previous List") Dim i As Integer, i2 As Integer If ffSelect.Result = "Select an Option" Then Call unHighlighter Exit Function ElseIf ffSelect.Result = "..Next List..." Then ffSelect.Select With ffSelectList .Clear .Add "Select an Option" Application.DisplayStatusBar = True Application.StatusBar = "Please wait while Word rebuild the dropdown list..." For i2 = LBound(Data2) To UBound(Data2) .Add Data2(i2) Next i2 End With ffSelectDropdown.Value = 1 nGenCount = True Application.DisplayStatusBar = False Call Highlighter DoEvents SendKeys "%({DOWN})", True Call ffGenerator ElseIf ffSelect.Result = "...Previous List" Then ffSelect.Select With ffSelectList .Clear .Add "Select an Option" Application.DisplayStatusBar = True Application.StatusBar = "Please wait while Word rebuild the dropdown list..." For i = LBound(Data) To UBound(Data) .Add Data(i) Next i End With ffSelectDropdown.Value = 1 nGenCount = True Application.DisplayStatusBar = False Call Highlighter DoEvents SendKeys "%({DOWN})", True Call ffGenerator Else nGenCount = False Call unHighlighter End If Exit Function ErrerHandel: MsgBox Err.Description, 64, Err.Number Exit Function End Function Public Sub AOnExit() Set ffSelect = ActiveDocument.FormFields(BKMName) If nGenCount = True Then Call Highlighter Exit Sub Else If ffSelect.Result = "...Previous List" Then Call ffGenerator Exit Sub ElseIf ffSelect.Result = "..Next List..." Then Call ffGenerator Exit Sub Else Call unHighlighter Exit Sub End If End If End Sub Public Function Highlighter() If ActiveDocument.ProtectionType <> wdNoProtection Then bprotected = True ActiveDocument.Unprotect Password:="" End If HighlightSettings.HighlightColorIndex = wdYellow unHighlighted = False If bprotected = True Then ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="" End If End Function Public Function unHighlighter() If unHighlighted = True Then Exit Function If ActiveDocument.ProtectionType <> wdNoProtection Then bprotected = True ActiveDocument.Unprotect Password:="" End If HighlightSettings.HighlightColorIndex = wdNoHighlight unHighlighted = True If bprotected = True Then ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="" End If End Function

How to use:

  1. There are 5 tasks to do to get the following code working.
  2. 1) Give your Dropdown Form Field a bookmark name (which is choosen under it's properties menu), and have other Form Fields on the document (preferably before the Dropdown Form Field)
  3. 2) Set the "OnEntry" macro (under the properties of the Dropdown Form Field) to "AOnEntry"
  4. 3) Set the "OnExit" macro (again, under the properties of the Dropdown Form Field) to "AOnExit".
  5. 4) Add any values to each Dropdown Form field list, but you must have the last item as "..Next List..."
  6. 5) Make sure your document is protected (Tools>Protect Document) and you are now able to use the macros
 

Test the code:

  1. Make sure you have completed the steps above and are currently not in a Dropdown form field when you protect the document.
  2. Click the Dropdown Form Field.
  3. Choose "..Next List..."
  4. The Form field should be highlighted in yellow and the drop down menu appear again with new values.
 

Sample File:

Dropdown Re-Generator Original code example.zip 14.67KB 

Approved by mdmackillop


This entry has been viewed 166 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express