View Single Post
 
Old 06-13-2021, 11:28 AM
brent chadwick brent chadwick is offline Windows 8 Office 2013
Advanced Beginner
 
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