Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
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
Reply



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 03:02 PM.


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