Help with Case and Select case

brent chadwick · #1 10-05-2015, 06:01 PM

As far as #2 I meant use text instead of the character-

macropod · #2 10-05-2015, 06:52 PM

I used Chr(148) because experience has taught me the VBE tends to transform ” into ".

brent chadwick · #3 10-06-2015, 05:17 AM

Got it. Now I understand that you just manually created a dropdown menu whereas I have to use code for the dropdown because I am inserting everything via macro. I thought you had some hidden code somewhere that I was unaware of. Thanks Paul, I have learned much from the master-

macropod · #4 10-06-2015, 05:40 AM

If you need to add the dropdown programmatically, you could use a separate macro, coded along the lines of:

Code:

Sub CreateDropDown()
Application.ScreenUpdating = False
Dim Prot As Variant, Rng As Range, FmFld As FormField
Const Pwd As String = ""
With ActiveDocument
  Prot = .ProtectionType
  If .ProtectionType <> wdNoProtection Then
    Prot = .ProtectionType
    .Unprotect Password:=Pwd
  End If
  Set Rng = ???
  Rng.InsertBefore Chr(147)
  Rng.Collapse wdCollapseEnd
  Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormDropDown)
   With FmFld
    .Name = "RecordsDD"
    .EntryMacro = ""
    .ExitMacro = "ConditionalContent"
    .Enabled = True
    With .DropDown.ListEntries
      .Add Name:="Record Collection"
      .Add Name:="U.S. Public Records Index"
      .Add Name:="United States Public Records 1970-2010"
    End With
  End With
  .Protect Type:=Prot, Password:=Pwd, NoReset:=True
End With
Set FmFld = Nothing: Set Rng = Nothing
Application.ScreenUpdating = True
End Sub

As you'll see, I've basically left the insertion range undefined; you'll have to replace the ??? with an appropriate definition.

brent chadwick · #5 10-06-2015, 12:13 PM

Thanks Paul, good talking to you-

brent chadwick · #6 10-14-2015, 03:53 AM

For Setting Range I used

Code:

Set Rng = Selection.Range

and it gives me a "Run time error 4120." What am I doing wrong?

brent chadwick · #7 10-14-2015, 04:12 AM

So I messed with a little bit and I fixed it but have no idea how, here's the code that works:

Code:

Option Explicit
Const BmkCCBmk As String = "CCBookmark" 'bookmark name
Const Pwd As String = "" 'Filling in Forms password

Sub PublicRecordsCombinedMacro()
'PublicRecordsCombinedMacro Macro
Application.ScreenUpdating = False
Dim Prot As Variant, Rng As Range, FmFld As FormField
With ActiveDocument
  Prot = .ProtectionType
  If .ProtectionType <> wdNoProtection Then
    Prot = .ProtectionType
    .Unprotect
   End If
Set Rng = Selection.Range
With ActiveDocument
  Rng.Collapse wdCollapseStart
  'Dropdown Menu for Record Collection
  Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormDropDown)
  With FmFld
    .Name = "RecordsDD"
    .EntryMacro = ""
    .ExitMacro = "CondtionalContent"
    .Enabled = True
    With .DropDown.ListEntries
      .Add Name:="Record Collection"
      .Add Name:="U.S. Public Records Index"
      .Add Name:="United States Public Records 1970-2010"
    End With
  End With
  With Rng
    .End = FmFld.Range.End
    .InsertBefore Chr(210)
    .InsertAfter ","
    .Collapse wdCollapseEnd
  End With
  With Rng
    .End = FmFld.Range.End
    .Collapse wdCollapseEnd
  End With
  .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=Pwd
  End With
  Set FmFld = Nothing: Set Rng = Nothing
Application.ScreenUpdating = True
End With
End Sub

Sign me stumped-

macropod · #8 10-14-2015, 04:28 AM

Quote:

Originally Posted by brent chadwick

So I messed with a little bit

No kidding!

Code:

Const BmkCCBmk As String = "CCBookmark" 'bookmark name
Const Pwd As String = "" 'Filling in Forms password
 
Sub PublicRecordsCombinedMacro()
'PublicRecordsCombinedMacro Macro
Application.ScreenUpdating = False
Dim Prot As Variant, Rng As Range, FmFld As FormField
With ActiveDocument
  Prot = .ProtectionType
  If .ProtectionType <> wdNoProtection Then
    Prot = .ProtectionType
    .Unprotect
   End If
  Set Rng = Selection.Range
  Rng.Collapse wdCollapseStart
  'Dropdown Menu for Record Collection
  Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormDropDown)
  With FmFld
    .Name = "RecordsDD"
    .EntryMacro = ""
    .ExitMacro = "CondtionalContent"
    .Enabled = True
    With .DropDown.ListEntries
      .Add Name:="Record Collection"
      .Add Name:="U.S. Public Records Index"
      .Add Name:="United States Public Records 1970-2010"
    End With
  End With
  With Rng
    .End = FmFld.Range.End
    .InsertBefore Chr(210)
    .InsertAfter ","
    .Collapse wdCollapseEnd
  End With
  With Rng
    .End = FmFld.Range.End
    .Collapse wdCollapseEnd
  End With
  .Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=Pwd
End With
Set FmFld = Nothing: Set Rng = Nothing
Application.ScreenUpdating = True
End Sub

macropod · #9 10-14-2015, 04:01 AM

Does the document you're trying to add the formfield to contain the ConditionalContent macro?

brent chadwick · #10 10-14-2015, 04:03 AM

Yes it is-

macropod · #11 10-14-2015, 04:11 AM

Well, I'm not getting any errors with the code as provided, when using:
Set Rng = Selection.Range
and the ConditionalContent macro is present.

macropod · #12 10-14-2015, 05:53 AM

For each formfield you want to trigger the unlinking with, add the line:
FmFld.ExitMacro = "Unlink"
after the last existing line referencing that formfield. Supplement that with the following macro:

Code:

Sub Unlink()
With ActiveDocument
  If .ProtectionType <> wdNoProtection Then
    .Unprotect Password:=Pwd
  End If
  .Fields.Unlink
End With
End Sub

brent chadwick · #13 10-15-2015, 09:21 AM

Thanks Paul, I have learned much from the master-again-

brent chadwick · #14 10-18-2015, 04:40 AM

Quick question, is it possible to have Italic and regular font in a dropdown menu? Thanks-

macropod · #15 10-18-2015, 12:52 PM

Quick answer: No.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Replace & case	Jennifer Murphy	Word	1	02-11-2013 03:26 AM
Question about Case statement	Jennifer Murphy	Word VBA	1	01-05-2013 02:30 PM
Case Sensitive (contains) Selection	apolloman	Excel	2	07-12-2011 04:50 AM
From all UPPER CASE to Proper Case	davers	Word	1	04-30-2009 12:41 PM
Upper to lower case	jd	Excel	1	04-28-2006 07:40 AM