Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-30-2015, 06:33 AM
brent chadwick brent chadwick is offline Windows 8 Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 76
brent chadwick is on a distinguished road
Default Help with Case and Select case

Hey guys, I'm working on cases that are dropdown menu dependent and I'm stuck on the code. I have extracted some from an other macro, but am now over my head. The two cases below are controlled by the dropdown menu. Here's the code.

Code:
Sub ConditionalContentPublicRecords()
'
' ConditionalContent Macro
Application.ScreenUpdating = False
Dim Prot As Variant, BkMrkRng As Range, Rng As Range, FmFld As FormField
Dim StrFmt As String, CCBmk As Boolean
With ActiveDocument
  Prot = .ProtectionType
Application.ScreenUpdating = False
Select Case Selection.FormFields("RecordsDD").Result
Case "U.S. Public Records Index": CCBmk = True: StrFmt = " vol."
Case "United States Public Records 1970-2010": CCBmk = True: StrFmt = " database,"
Case Else: CCBmk = False: StrFmt = ""
End Select
If .ProtectionType <> wdNoProtection Then
    Prot = .ProtectionType
    .Unprotect
    'Update the bookmark referenced via BmkPopn
    Set BkMrkRng = .Bookmarks(CCBmk).Range
    If (CCBmk = True) And (Len(BkMrkRng.Text) = 0) Or _
      (CCBmk = False) And (Len(BkMrkRng.Text) <> 0) Then
      If (CCBmk = True) And (Len(BkMrkRng.Text) = 0) Then
        BkMrkRng.Text = ", "
      Else
        BkMrkRng.Delete
      End If
      .Bookmarks.Add CCBmk, BkMrkRng
    End If
    Select Case .FormFields("RecordsDD").Result
    
Case "U.S. Public Records Index"
If InStr(BkMrkRng.Text, ", vol. ") = 0 Then
          'Text & Formfield for volume
          With Rng
            .Text = StrFmt & ", vol. "
            .Collapse wdCollapseEnd
          End With
          Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormTextInput)
          FmFld.TextInput.EditType wdRegularText, "X"
          With Rng
    .Collapse wdCollapseEnd
    .InsertAfter ", "
    .End = FmFld.Range.End
    .Collapse wdCollapseEnd
    ' Ancestry Text and Viewing Record Date
    .InsertAfter " Ancestry (http://www.ancestry.com : viewed " & _
      Format(Now, "d MMMM yyyy") & "),"
    .Words(4).Font.Italic = True
    .Collapse wdCollapseEnd
    .InsertAfter ", entry for"
    'Name Form Field
  End With
    Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormTextInput)
    FmFld.TextInput.EditType wdRegularText, "Name"
    With Rng
    .Collapse wdCollapseEnd
    .InsertAfter ", "
    .End = FmFld.Range.End
    .Collapse wdCollapseEnd
    Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormTextInput)
    FmFld.TextInput.EditType wdRegularText, "Place"
    'Place Form Field
    With FmFld
    .Name = "Place"
    .EntryMacro = ""
    .ExitMacro = "UnlinkFields"
    .Enabled = True
    .TextInput.EditType Type:=wdRegularText, Default:="X", Format:="Title case"
    .TextInput.Width = 0
  End With
  'Additional Text
    With Rng
    .Collapse wdCollapseEnd
    .InsertAfter ", citing "" voter registration lists, public record filings, historical residential records, and other household database listings."""
    .End = FmFld.Range.End
    .Collapse wdCollapseEnd
    
    Select Case .FormFields("RecordsDD").Result

    
    Case "United States Public Records 1970-2010"
    If (InStr(BkMrkRng.Text, "database") = 0) Then
    With Rng
    .Text = StrFmt & ", database, "
    .Collapse wdCollapseEnd
    .InsertAfter "FamilySearch.org (https://www.familysearch.org/search/collection/2199956 : viewed " & _
    Format(Now, "d MMMM yyyy") & "), citing ""telephone directories, property tax assessments, credit applications, and other records available to the public,"" entry for """
    .Words(4).Font.Italic = True
    .Collapse wdCollapseEnd
    End With
    'Firstname Lastname Form Field
    Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormTextInput)
    With FmFld
    .Name = "FirstnameLastname"
    .EntryMacro = ""
    .ExitMacro = "UnlinkFields"
    .Enabled = True
    .TextInput.EditType Type:=wdRegularText, Default:="X", Format:="Title case"
    .TextInput.Width = 0
    End With
    With Rng
    .End = FmFld.Range.End
    .InsertAfter "."
    .Collapse wdCollapseEnd
    End With
    'Case Else: BkMrkRng.Text = ""
    End Select
    If Not FmFld Is Nothing Then BkMrkRng.End = FmFld.Range.End
    End If
    .Protect Type:=Prot, Password:=Pwd, NoReset:=True
  End If
End With
Set FmFld = Nothing: Set Rng = Nothing: Set BkMrkRng = Nothing
Application.ScreenUpdating = True
End Sub

Reply With Quote
  #2  
Old 10-01-2015, 10:17 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,110
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

To put it politely, your code is a dog's breakfast. You have:
• Application.ScreenUpdating = False used twice
• a bookmark apparently named CCBmk but defined as a Boolean
• bits of Case tests that are not part of a Select Case structure,
and so on.

I could mess around correcting all that, but I can't even tell from that mess what the code is supposed to do. Perhaps you'd care to explain?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 10-01-2015, 11:09 PM
gmayor's Avatar
gmayor gmayor is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 2,673
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

You might also explain what 'rng' refers to.
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #4  
Old 10-02-2015, 04:20 AM
brent chadwick brent chadwick is offline Windows 8 Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 76
brent chadwick is on a distinguished road
Default

Yeah, I know it's a mess, that's why I'm asking for help from people that have skills way beyond mine. I tried to take code from another one that you guys helped me with-didn't work. Here's what the code is supposed to do-

"U.S. Public Records Index, vol. X," Ancestry (http://www.ancestry.com : viewed 5 September 2015), entry for Name, Place, citing "voter registration lists, public record filings, historical residential records, and other household database listings."


"United States Public Records 1970-2010," database, FamilySearch.org (https://familysearch.org/search/collection/2199956 : viewed 5 September 2015), citing "telephone directories, property tax assessments, credit applications, and other records available to the public, entry for Firstname Lastname.

These two need to be combined, the dropdown menu being the text in quotes. Here's the code (such as it is) for the complete macro-

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 Rng As Range, FmFld As FormField, i As Long
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 = "ConditionalContentPublicRecords"
    .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 """"
    .InsertAfter ","
    .Collapse wdCollapseEnd
  End With
  .Bookmarks.Add Name:=BmkCCBmk, Range:=Rng
  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

Sub ConditionalContentPublicRecords()
'
' ConditionalContent Macro
Application.ScreenUpdating = False
Dim Prot As Variant, BkMrkRng As Range, Rng As Range, FmFld As FormField
Dim StrFmt As String, CCBmk As Boolean
With ActiveDocument
  Prot = .ProtectionType
Application.ScreenUpdating = False
Select Case Selection.FormFields("RecordsDD").Result
Case "U.S. Public Records Index": CCBmk = True: StrFmt = " vol."
Case "United States Public Records 1970-2010": CCBmk = True: StrFmt = " database,"
Case Else: CCBmk = False: StrFmt = ""
End Select
If .ProtectionType <> wdNoProtection Then
    Prot = .ProtectionType
    .Unprotect
    'Update the bookmark referenced via BmkPopn
    Set BkMrkRng = .Bookmarks(CCBmk).Range
    If (CCBmk = True) And (Len(BkMrkRng.Text) = 0) Or _
      (CCBmk = False) And (Len(BkMrkRng.Text) <> 0) Then
      If (CCBmk = True) And (Len(BkMrkRng.Text) = 0) Then
        BkMrkRng.Text = ", "
      Else
        BkMrkRng.Delete
      End If
      .Bookmarks.Add CCBmk, BkMrkRng
    End If
    Select Case .FormFields("RecordsDD").Result
    
Case "U.S. Public Records Index"
If InStr(BkMrkRng.Text, ", vol. ") = 0 Then
          'Text & Formfield for volume
          With Rng
            .Text = StrFmt & ", vol. "
            .Collapse wdCollapseEnd
          End With
          Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormTextInput)
          FmFld.TextInput.EditType wdRegularText, "X"
          With Rng
    .Collapse wdCollapseEnd
    .InsertAfter ", "
    .End = FmFld.Range.End
    .Collapse wdCollapseEnd
    ' Ancestry Text and Viewing Record Date
    .InsertAfter " Ancestry (http://www.ancestry.com : viewed " & _
      Format(Now, "d MMMM yyyy") & "),"
    .Words(4).Font.Italic = True
    .Collapse wdCollapseEnd
    .InsertAfter ", entry for"
    'Name Form Field
  End With
    Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormTextInput)
    FmFld.TextInput.EditType wdRegularText, "Name"
    With Rng
    .Collapse wdCollapseEnd
    .InsertAfter ", "
    .End = FmFld.Range.End
    .Collapse wdCollapseEnd
    Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormTextInput)
    FmFld.TextInput.EditType wdRegularText, "Place"
    'Place Form Field
    With FmFld
    .Name = "Place"
    .EntryMacro = ""
    .ExitMacro = "UnlinkFields"
    .Enabled = True
    .TextInput.EditType Type:=wdRegularText, Default:="X", Format:="Title case"
    .TextInput.Width = 0
  End With
  'Additional Text
    With Rng
    .Collapse wdCollapseEnd
    .InsertAfter ", citing "" voter registration lists, public record filings, historical residential records, and other household database listings."""
    .End = FmFld.Range.End
    .Collapse wdCollapseEnd
    
    Select Case .FormFields("RecordsDD").Result

    
    Case "United States Public Records 1970-2010"
    If (InStr(BkMrkRng.Text, "database") = 0) Then
    With Rng
    .Text = StrFmt & ", database, "
    .Collapse wdCollapseEnd
    .InsertAfter "FamilySearch.org (https://www.familysearch.org/search/collection/2199956 : viewed " & _
    Format(Now, "d MMMM yyyy") & "), citing ""telephone directories, property tax assessments, credit applications, and other records available to the public,"" entry for """
    .Words(4).Font.Italic = True
    .Collapse wdCollapseEnd
    End With
    'Firstname Lastname Form Field
    Set FmFld = .FormFields.Add(Range:=Rng, Type:=wdFieldFormTextInput)
    With FmFld
    .Name = "FirstnameLastname"
    .EntryMacro = ""
    .ExitMacro = "UnlinkFields"
    .Enabled = True
    .TextInput.EditType Type:=wdRegularText, Default:="X", Format:="Title case"
    .TextInput.Width = 0
    End With
    With Rng
    .End = FmFld.Range.End
    .InsertAfter "."
    .Collapse wdCollapseEnd
    End With
    'Case Else: BkMrkRng.Text = ""
    End Select
    If Not FmFld Is Nothing Then BkMrkRng.End = FmFld.Range.End
    End If
    .Protect Type:=Prot, Password:=Pwd, NoReset:=True
  End If
End With
Set FmFld = Nothing: Set Rng = Nothing: Set BkMrkRng = Nothing
Application.ScreenUpdating = True
End Sub
Trying to learn, thanks for your help.
Reply With Quote
  #5  
Old 10-02-2015, 05:05 AM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,110
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by brent chadwick View Post
Yeah, I know it's a mess, that's why I'm asking for help from people that have skills way beyond mine. I tried to take code from another one that you guys helped me with-didn't work.
Do you mean: http://www.msofficeforums.com/word-v...html#post85712 ? If so, is the following supposed to be integrated into the existing dropdown?
Quote:
Here's what the code is supposed to do-

"U.S. Public Records Index, vol. X," Ancestry (http://www.ancestry.com : viewed 5 September 2015), entry for Name, Place, citing "voter registration lists, public record filings, historical residential records, and other household database listings."


"United States Public Records 1970-2010," database, FamilySearch.org (https://familysearch.org/search/collection/2199956 : viewed 5 September 2015), citing "telephone directories, property tax assessments, credit applications, and other records available to the public, entry for Firstname Lastname.

These two need to be combined, the dropdown menu being the text in quotes.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #6  
Old 10-03-2015, 09:09 AM
brent chadwick brent chadwick is offline Windows 8 Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 76
brent chadwick is on a distinguished road
Default

If the question you are asking is for the two new source citations to be integrated into the other post, the answer is no. These two are different source citations. What I tried to do with the other post is try and figure out what I could extract and use for the these two new ones. However, the other code in the other post is quite complicated and I could only figure out some things. I have several more of these citations that start out with a dropdown menu followed by text, form fields and of course the web site. Thanks for your help-
Reply With Quote
  #7  
Old 10-03-2015, 01:58 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,110
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Perhaps, then, you could attach a document to a post showing what it is you're trying to achieve.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #8  
Old 10-03-2015, 03:53 PM
brent chadwick brent chadwick is offline Windows 8 Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 76
brent chadwick is on a distinguished road
Default

Here's the doc-blue is the dropdown menu, yellow is the form fields(text), and the rest is conditional text-thanks for your help-
Attached Files
File Type: docx Record Collection Citation.docx (45.6 KB, 1 views)
Reply With Quote
  #9  
Old 10-03-2015, 09:03 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,110
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

See attached.
Attached Files
File Type: docm Record Collection Citation.docm (26.9 KB, 7 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #10  
Old 10-05-2015, 06:48 AM
brent chadwick brent chadwick is offline Windows 8 Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 76
brent chadwick is on a distinguished road
Default

Thanks Paul, I have many questions, I will try to limit them.
1) Where does the dropdown menu come from?
2) What is Chr(148)?
3) What are Private Sub Documents?
Learning, lots more to learn-
Reply With Quote
  #11  
Old 10-05-2015, 08:23 AM
brent chadwick brent chadwick is offline Windows 8 Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 76
brent chadwick is on a distinguished road
Default

I'm working on my Mac and instead of the " after the dropdown menu (which I think is Chr(148)), I get a " î". What's up with this?
Reply With Quote
  #12  
Old 10-05-2015, 08:30 AM
brent chadwick brent chadwick is offline Windows 8 Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 76
brent chadwick is on a distinguished road
Default

Got it, it's an ascII character and for the Mac it's 211-learning new things-

Reply With Quote
  #13  
Old 10-05-2015, 02:26 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,110
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by brent chadwick View Post
1) Where does the dropdown menu come from?
2) What is Chr(148)?
3) What are Private Sub Documents?
1: It's just an ordinary dropdown formfield that I added to the document. No code involved.
2: A closing double-quote character
3: There is no such code in the document I posted. Amongst other things, the 'Private' directive hides a Sub from the GUI
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #14  
Old 10-05-2015, 02:31 PM
brent chadwick brent chadwick is offline Windows 8 Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 76
brent chadwick is on a distinguished road
Default

1) What I want to know is how you did #1
2) Why not just insert text? Preference?
3) I have no clue what a GUI is-
Reply With Quote
  #15  
Old 10-05-2015, 03:12 PM
macropod's Avatar
macropod macropod is online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,110
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

1: Developer>Controls>Legacy Controls>Drop-Down Form Field.
2. Without a dropdown how would you propose to choose the option that governs the text?
3: Graphical User Interface.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 01:44 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft