Microsoft Office Forums - View Single Post

one4youman · 04-16-2019, 06:26 AM

I am working on trying to create a template letter that uses bookmarks to generate a UserForm and then selections on the UserForm are then used to update the template.

The letter contains language associated with multiple different contracts (Contract 1, Contract 2, Contract 3, etc.) which each contain 15 to 25 different contractual terms.

Within the UserForm you would select the Contract from a dropdown Combo Box and based on this selection the 15 to 25 different contractual terms would load as a selectable CheckBox. After selecting the CheckBoxes that you want to cite in the letter, you would click an OK button which would clear any terms that are not selected.

The language and selections are different for each contract, and contain very specific language / format that need to be followed. The letter template would initially contain all of the contracts and based on the UserForm the contracts that are not selected are deleted followed by the terms that are not selected and achieved by creating bookmarks for each of the terms that will also be contained within a bookmark for the contract.

The initial template text would be as follows:

<Begin Bookmark Contract1>
Introduction text / message about the contract.

<Begin Bookmark Contract1Term1>
Term 1. Explanation of the term.
<End Bookmark Contract1Term1>

<Begin Bookmark Contract1Term2>
Term 2. Explanation of the term.
<End Bookmark Contract1Term2>

<Begin Bookmark Contract1Term3>
Term 3. Explanation of the term.:
· Explanation of the term.
· Explanation of the term.
· Explanation of the term.
<End Bookmark Contract1Term3>

<End Bookmark Contract1>

<Begin Bookmark Contract2>
Introduction text / message about the contract.

<Begin Bookmark Contract2Term1>
1. Name of term1
a. Part A Explanation of the term:
(1) Explanation of the term;
(2) Explanation of the term;
b. Part B Explanation of the term.
<End Bookmark Contract2Term1>

<Begin Bookmark Contract2Term2>
2. Name of term2
a. Part A Explanation of the term:
(1) Explanation of the term;
(2) Explanation of the term;
b. Part B Explanation of the term.
<End Bookmark Contract2Term2>

<End Bookmark Contract2>

UserForm / Script:
On the UserForm I have 25 CheckBox’s (Labeled 1 through 25) listed and I would like the caption to be updated to reflect a portion of the term. I have been able to script this to obtain the text to the left of the first period, however on some of the contracts the term starts with a number then a period followed by the term text. Ideally I would like to extract only the first portion of the string that appears in Bold without extracting any other portions of the string that may also appear in Bold.

I would then like to pass the remainder of the string to the ControlTipText field so the text can be seen during a mouse over of the text box.

I also want to pass the string source (Name of the bookmark) to the Checkbox Tag so I can use it to later reference the Bookmark name to determine which book marks to keep, and which to delete.

If no bookmark is attached to the checkbox during the loop, I will then hide the checkbox from the Userform.

Any suggestions on how to change the below code to extract only the first portion of the bold text from each bookmark in the below script to pass it to the caption?

Code:

Private Sub CommandButton8_Click()
    Dim bmk As Bookmark
    Dim i As Long
    Dim P As String
    Dim CovOutput As String
    Dim msg As String
        i = 1
        For Each bmk In ActiveDocument.Bookmarks("Contract1").Range.Bookmarks
            
            CovOutput = bmk.Name
            P = i
            
            Controls("CheckBox" & P).Tag = bmk.Name 'Set Tag equal to the bookmark name - Extract Tag to determine which to remove on false statement.
            Controls("CheckBox" & P).Caption = Left(ActiveDocument.Bookmarks(CovOutput).Range.Text, InStr(ActiveDocument.Bookmarks(CovOutput).Range.Text, ".")) 'Text Prior to 1st Period
            Controls("CheckBox" & P).ControlTipText = Mid(ActiveDocument.Bookmarks(CovOutput).Range.Text, (InStr(ActiveDocument.Bookmarks(CovOutput).Range.Text, ".") + 1)) 'Remaining text from the string
                msg = msg & bmk.Name & vbCr 'Adds bookmark names to Msgbox to use during debug.
                    
            i = (i + 1)
            On Error GoTo ErrorStop
        Next bmk
ErrorStop:
    
    'Uncomment for Debug
    'MsgBox msg
End Sub

The above works to extract the text based on the first period, but will not work for all of the contracts as each is slightly different. Instead I would like to extract the first few words that appear in bold within the bookmark and then pass the text to the caption.

IE:
<Begin Bookmark Contract1Term1>
Term 1. Explanation of the term.
<End Bookmark Contract1Term1>

would pass text "Term 1." to the caption.

OR

<Begin Bookmark Contract2Term1>
1. Name of term1
a. Part A Explanation of the term:
(1) Explanation of the term;
(2) Explanation of the term;
b. Part B Explanation of the term.
<End Bookmark Contract2Term1>

would pass text "1. Name of term1" to the caption.

Thanks,
John