Help with Case and Select case

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

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

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

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

brent chadwick · #18 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 · #19 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 · #20 10-06-2015, 12:13 PM

Thanks Paul, good talking to you-

brent chadwick · #21 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?

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

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

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

Yes it is-

macropod · #24 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.

brent chadwick · #25 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 · #26 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

brent chadwick · #27 10-14-2015, 05:17 AM

All of these citations I need to have an exit macro on the last form field to unlink the fields. Since the way you have shown me to write this code is different, do you have a spot where I can insert into the code? Learning this way is painful-

macropod · #28 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 · #29 10-15-2015, 09:21 AM

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

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

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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