Extract only the first portion of bold text from text string

one4youman

New Member
Joined
Oct 5, 2018
Messages
13
While this is not being implemented within Excel, it does involve the use of strings similar to those used within Excel and I am hopeful someone can assist.

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><begin bookmark="" contract1="">
Introduction text / message about the contract.

<begin bookmark="" contract1term1=""><Begin Bookmark Contract1Term1>
Term 1. Explanation of the term.
<end bookmark="" contract1term1=""><End Bookmark Contract1Term1>

<begin bookmark="" contract1term2=""><Begin Bookmark Contract1Term2>
Term 2. Explanation of the term.
<end bookmark="" contract1term2=""><End Bookmark Contract1Term2>

<begin bookmark="" contract1term3=""><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 Contract1Term3>

<End Bookmark Contract1>
<end bookmark="" contract1="">]

[
<begin bookmark="" contract2=""><Begin Bookmark Contract2>
Introduction text / message about the contract.

<begin bookmark="" contract2term1=""><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=""><End Bookmark Contract2Term1>

<begin bookmark="" contract2term2=""><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 Contract2Term2>

<End Bookmark Contract2>
<end bookmark="" contract2="">]

UserForm / Script:
On the UserForm I have 25 CheckBox’s 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.

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))
                msg = msg & bmk.Name & vbCr
            i = (i + 1)
            On Error GoTo ErrorStop
        Next bmk


ErrorStop:
    
    'Uncomment for Debug
    MsgBox msg


End Sub

Thank you in advance,
John</end></end></begin></end></begin></begin></end></end></begin></end></begin></end></begin></begin>
 
Last edited:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top