Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-12-2021, 07:37 AM
brent chadwick brent chadwick is offline Work around for more than 25 items in drop-down menu Windows 8 Work around for more than 25 items in drop-down menu Office 2013
Advanced Beginner
Work around for more than 25 items in drop-down menu
 
Join Date: Mar 2015
Posts: 86
brent chadwick is on a distinguished road
Default Work around for more than 25 items in drop-down menu

Does anybody have a work around for having more than 25 items in a drop-down menu? I have a macro that I need to write that has 62 items. Suggestions?
Reply With Quote
  #2  
Old 06-12-2021, 03:11 PM
macropod's Avatar
macropod macropod is offline Work around for more than 25 items in drop-down menu Windows 10 Work around for more than 25 items in drop-down menu Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Word formfield dropdowns have a design limit of 25 entries. You cannot circumvent that.

Word content control dropdowns do not have such a design limit.

However, formfields and content controls should not be used in the same document. They were not designed to be used that way and trying to do so is a known source of usability problems.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 06-12-2021, 03:54 PM
brent chadwick brent chadwick is offline Work around for more than 25 items in drop-down menu Windows 8 Work around for more than 25 items in drop-down menu Office 2013
Advanced Beginner
Work around for more than 25 items in drop-down menu
 
Join Date: Mar 2015
Posts: 86
brent chadwick is on a distinguished road
Default

So there are no other alternatives? How about a macro that has three drop-down selections that split the 62 into three parts and then uses cases for the choices?

Would cascading drop-downs work? Just spitballing here-thanks Paul.
Reply With Quote
  #4  
Old 06-12-2021, 06:45 PM
macropod's Avatar
macropod macropod is offline Work around for more than 25 items in drop-down menu Windows 10 Work around for more than 25 items in drop-down menu Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Since I have no idea of how the dropdown options might relate to each other, it's impossible to say what alternatives you might have. One thing is almost inevitable: any solution that enables a workaround will require the use of a macro.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 06-13-2021, 11:28 AM
brent chadwick brent chadwick is offline Work around for more than 25 items in drop-down menu Windows 8 Work around for more than 25 items in drop-down menu Office 2013
Advanced Beginner
Work around for more than 25 items in drop-down menu
 
Join Date: Mar 2015
Posts: 86
brent chadwick is on a distinguished road
Default

I have several macros that insert simple text for county and state. The problem is that New York State has 62 counties. Here is the macro for Massachusetts: when the macro is fired the drop-down menu says "Massachusetts Counties." You can select the county you need and it inserts the county name and state. Here is the code for Massachusetts-
Code:
Option Explicit
Sub Massachusetts()
'Mass State/County Macro
Application.ScreenUpdating = False
Dim Rng As Range, FmFld As FormField
Set Rng = Selection.Range
With ActiveDocument
  Rng.Collapse wdCollapseStart
 'Dropdown Menu for Massachusetts Counties
  Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormDropDown)
  With FmFld
    .Name = "MassDD"
    .EntryMacro = ""
    .ExitMacro = "MassachusettsCC"
    .Enabled = True
    With .DropDown.ListEntries
     .Add Name:="Massachusetts Counties"
    .Add Name:="Barnstable"
     .Add Name:="Berkshire"
      .Add Name:="Bristol"
      .Add Name:="Dukes"
      .Add Name:="Essex"
      .Add Name:="Franklin"
      .Add Name:="Hampden"
      .Add Name:="Hampshire"
      .Add Name:="Middlesex"
      .Add Name:="Nantucket"
      .Add Name:="Norfolk"
      .Add Name:="Plymouth"
      .Add Name:="Suffolk"
      .Add Name:="Worcester"
    End With
  End With
  With Rng
    .End = FmFld.Range.End
    .Collapse wdCollapseEnd
  End With
   .Protect Type:=wdAllowOnlyFormFields, NoReset:=True
  End With
  Set FmFld = Nothing: Set Rng = Nothing
Application.ScreenUpdating = True
End Sub
Sub MassachusettsCC()
Application.ScreenUpdating = False
Dim Prot As Variant, Rng As Range, FmFld As FormField, Bookmark As Bookmark, Range As Word.Range, lngIndex As Long
Const Pwd As String = ""
With ActiveDocument
  Prot = .ProtectionType
  If .ProtectionType <> wdNoProtection Then
    Prot = .ProtectionType
    .Unprotect Password:=Pwd
    Set Rng = Selection.Range
    Select Case .FormFields("MassDD").Result
        'Case For Barnstable County
      Case "Barnstable"
      If InStr(Rng.Text, "Barnstable") = 0 Then
      With Rng
            .Text = "Barnstable County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
        'Case For Berkshire County
      Case "Berkshire"
      If InStr(Rng.Text, "Berkshire") = 0 Then
      With Rng
            .Text = "Berkshire County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
        'Case For Bristol County
      Case "Bristol"
      If InStr(Rng.Text, "Bristol") = 0 Then
      With Rng
            .Text = "Bristol County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
        'Case For Dukes County
      Case "Dukes"
      If InStr(Rng.Text, "Dukes") = 0 Then
      With Rng
            .Text = "Dukes County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
        'Case For Essex County
      Case "Essex"
      If InStr(Rng.Text, "Essex") = 0 Then
      With Rng
            .Text = "Essex County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
        'Case For Franklin County
      Case "Franklin"
      If InStr(Rng.Text, "Franklin") = 0 Then
      With Rng
            .Text = "Franklin County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
        'Case For Hampden County
      Case "Hampden"
      If InStr(Rng.Text, "Hampden") = 0 Then
      With Rng
            .Text = "Hampden County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
        'Case For Hampshire County
      Case "Hampshire"
      If InStr(Rng.Text, "Hampshire") = 0 Then
      With Rng
            .Text = "Hampshire County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
        'Case For Middlesex County
      Case "Middlesex"
      If InStr(Rng.Text, "Middlesex") = 0 Then
      With Rng
            .Text = "Middlesex County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
        'Case For Nantucket County
      Case "Nantucket"
      If InStr(Rng.Text, "Nantucket") = 0 Then
      With Rng
            .Text = "Nantucket County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
        'Case For Norfolk County
      Case "Norfolk"
      If InStr(Rng.Text, "Norfolk") = 0 Then
      With Rng
            .Text = "Norfolk County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
        'Case For Plymouth County
      Case "Plymouth"
      If InStr(Rng.Text, "Plymouth") = 0 Then
      With Rng
            .Text = "Plymouth County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
        'Case For Suffolk County
      Case "Suffolk"
      If InStr(Rng.Text, "Suffolk") = 0 Then
      With Rng
            .Text = "Suffolk County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
        'Case For Worcester County
      Case "Worcester"
      If InStr(Rng.Text, "Worcester") = 0 Then
      With Rng
            .Text = "Worcester County, Massachusetts, "
            .Collapse wdCollapseEnd
           End With
           Selection.MoveRight Unit:=wdWord, Count:=5
           End If
      Case Else: Rng.Text = Chr(211)
    End Select
End If
End With
Set FmFld = Nothing: Set Rng = Nothing
Application.ScreenUpdating = True
End Sub
I would guess that with New York the 62 would have to be divided somehow-
Reply With Quote
  #6  
Old 06-13-2021, 04:42 PM
macropod's Avatar
macropod macropod is offline Work around for more than 25 items in drop-down menu Windows 10 Work around for more than 25 items in drop-down menu Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

So why are you using formfields instead of content controls?

That aside, you could have a county selector with 25 items, the last of which has 'page 2' and, when that item is selected, a macro clears the 'page 1' list and displays the 'page 2' list. The 'page 2' list would have 'page 1' as its first item (so you can go back) and a 'page 3' entry as the last item. Etc. The same approach could be taken at the state level, since you have more than 25 states to deal with. See attached.
Attached Files
File Type: docm Counties.docm (27.1 KB, 52 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 06-14-2021, 05:49 AM
brent chadwick brent chadwick is offline Work around for more than 25 items in drop-down menu Windows 8 Work around for more than 25 items in drop-down menu Office 2013
Advanced Beginner
Work around for more than 25 items in drop-down menu
 
Join Date: Mar 2015
Posts: 86
brent chadwick is on a distinguished road
Default

You would have to school me on content controls as I am clueless as to what they are, how they work and where to use them.
In the example you gave me, I 'm guessing that for each of the pages I would have an exit macro, and then use cases for the text formation of each county/state? Thanks for your help Paul-
Reply With Quote
  #8  
Old 06-14-2021, 03:10 PM
macropod's Avatar
macropod macropod is offline Work around for more than 25 items in drop-down menu Windows 10 Work around for more than 25 items in drop-down menu Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

In the example I posted, there is a single 'on exit macro', named 'Counties' that manages all the dropdown options.

For content controls, have a look at some of these examples:

Dependent Dropdown Content Controls
https://www.msofficeforums.com/word-...html#post77762
and, for multiple levels of dependency:
https://www.msofficeforums.com/word-...tml#post132696

Cascading Dropdown Content Controls
https://www.msofficeforums.com/word-...html#post94603

Dependent Text Content Controls
https://www.msofficeforums.com/word-...html#post46903
and, for different elements from a selected item to be output to different content controls, see:
https://www.msofficeforums.com/word-...tml#post120392


Dropdown Content Control Population from Excel
https://www.msofficeforums.com/word-...html#post46287

Creating and Tallying Mutually-exclusive Checkbox Content Controls
https://www.msofficeforums.com/word-...html#post33489
https://www.msofficeforums.com/word-...tml#post107008
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Work around for more than 25 items in drop-down menu Cascading drop-downs in Word with one parent menu and four dependent drop-downs mrstrawhand Word VBA 3 03-30-2020 06:08 AM
Work around for more than 25 items in drop-down menu Right Click Menu Missing Items vijer Excel 5 09-05-2016 04:44 AM
Work around for more than 25 items in drop-down menu Menu Items Bold a2727sreyes27 Word 2 12-15-2014 07:44 AM
Work around for more than 25 items in drop-down menu I can't see the items on the file menu... CaneRivero Word 2 08-14-2014 11:11 AM
Work around for more than 25 items in drop-down menu Missing calendar menu (navigation pane) items? carolfarm Outlook 1 07-18-2012 12:03 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:46 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft