Let bookmark check if there are empty bookmarks before and place sentence there?

RevoMedia · #1 01-14-2020, 12:42 AM

For a person im helping with his VBA, I have the following question:

I have 9 bookmarks down to each other.
I have 9 optionboxes in a UserForm. They all represent a sentence.
I select option 1 and 4, so sentence one and 4 are loaded into the document by the bookmarks.

But, there are empty spaces because optionbox 3, 4, 5, 6, 7, 8 and 9 are not checked.
Is it possible to let VBA look if there are optionboxes not checked, to load sentence 1 and 4 in bookmark 1 and 2, for example: when 9 is also checked it must be loaded to BookMark 3.

It is now:
| Sentence 1
|
|
| Sentence 4
|
|
|
|
| Sentence 9

but is need to be:
|Sentence 1
|Sentence 4
|Sentence 9
|

Hope you guys understand me because my english is a little bad.

Thanks in advance!

gmayor · #2 01-14-2020, 02:05 AM

It might make more sense to have just one bookmark (or a content control) to receive the text then loop through the check boxes and fill the bookmark with the text associated with the bookmarks that are checked. This is easier to follow if you use the default names for the checkboxes e.g as follows. Note the checkbox and bookmark names are case sensitive.

Code:

Private Sub CommandButton1_Click()
Dim oCTRL As Control
Dim sText As String: sText = ""
    Hide
    For Each oCTRL In Controls
        Select Case oCTRL.Name
            Case Is = "CheckBox1"
                If oCTRL.value = True Then
                    If sText = "" Then
                        sText = "Sentence 1"
                    End If
                End If
            Case Is = "CheckBox2"
                If oCTRL.value = True Then
                    If sText = "" Then
                        sText = "Sentence 2"
                    Else
                        sText = sText & vbCr & "Sentence 2"
                    End If
                End If
            Case Is = "CheckBox3"
                If oCTRL.value = True Then
                    If sText = "" Then
                        sText = "Sentence 3"
                    Else
                        sText = sText & vbCr & "Sentence 3"
                    End If
                End If
            Case Is = "CheckBox4"
                If oCTRL.value = True Then
                    If sText = "" Then
                        sText = "Sentence 4"
                    Else
                        sText = sText & vbCr & "Sentence 4"
                    End If
                End If
            Case Is = "CheckBox5"
                If oCTRL.value = True Then
                    If sText = "" Then
                        sText = "Sentence 5"
                    Else
                        sText = sText & vbCr & "Sentence 5"
                    End If
                End If
            Case Is = "CheckBox6"
                If oCTRL.value = True Then
                    If sText = "" Then
                        sText = "Sentence 6"
                    Else
                        sText = sText & vbCr & "Sentence 6"
                    End If
                End If
            Case Is = "CheckBox7"
                If oCTRL.value = True Then
                    If sText = "" Then
                        sText = "Sentence 7"
                    Else
                        sText = sText & vbCr & "Sentence 2"
                    End If
                End If
            Case Is = "CheckBox8"
                If oCTRL.value = True Then
                    If sText = "" Then
                        sText = "Sentence 8"
                    Else
                        sText = sText & vbCr & "Sentence 8"
                    End If
                End If
            Case Is = "CheckBox9"
                If oCTRL.value = True Then
                    If sText = "" Then
                        sText = "Sentence 9"
                    Else
                        sText = sText & vbCr & "Sentence 9"
                    End If
                End If
        End Select
    Next oCTRL
    'MsgBox sText
    FillBM "BookMark_Name", sText
    Unload Me
End Sub

You can then fill the bookmarks with the following function

Code:

Public Sub FillBM(strbmName As String, strValue As String)
'Graham Mayor - http://www.gmayor.com
Dim oRng As Range
    With ActiveDocument
        On Error GoTo lbl_Exit
        Set oRng = .Bookmarks(strbmName).Range
        oRng.Text = strValue
        oRng.Bookmarks.Add strbmName
    End With
lbl_Exit:
    Set oRng = Nothing
    Exit Sub
End Sub

Guessed · #3 01-14-2020, 03:37 PM

It appears that you are hiding text in bookmarks if the checkbox is not ticked. If that is the case, you just need to include the paragraph mark in each of the bookmarks.

Graham's suggestion is a good alternative but there is a trap there that you would need to consider. When looping through controls on a form, Word uses the order of creation, not the order from the top (if checkbox2 was created before checkbox1). If that is an issue, you can cut a checkbox and then paste it back in to make it the 'last' checkbox in the loop.

Following on Graham's method, I generally try to minimise the coding to make future updates easier by using a Concatenate function and making use of properties on each checkbox. This alternative to Graham's first macro could then be the following code

Code:

Private Sub CommandButton1_Click()
  Dim aCtl As Control, sResult As String
  For Each aCtl In Me.Controls
    If aCtl.Tag = "MyCheck" And aCtl = True Then
      sResult = Concat(sResult, aCtl.ControlTipText)
    End If
  Next aCtl
  FillBM "BookMark_Name", sResult
  Unload Me
End Sub

Private Function Concat(s1 As String, s2 As String, Optional sJoin As String = vbCr)
  If s1 = "" Then
    Concat = s2
  ElseIf s2 = "" Then
    Concat = s1
  Else
    Concat = s1 & sJoin & s2
  End If
End Function

gmayor · #4 01-14-2020, 11:36 PM

Andrew
Your point about insertion order is a good one, and the code could indeed be reduced along the lines you suggest, but could be harder for a beginner to follow.

The real problem with this approach is that the inserted text is not going to be 'Sentence1', 'Sentence2' etc., which is fairly easy to accommodate (see below); but real world sentences are not. We then have to consider the use of arrays to hold the texts, which adds more complexity - thus:

Code:

Private Sentence(8) As Variant

Private Sub CommandButton1_Click()
Dim aCtl As Control, sResult As String
Dim sText As String
    Sentence(0) = "This is the first sentence."
    Sentence(1) = "This is the second sentence."
    Sentence(2) = "This is the third sentence."
    Sentence(3) = "This is the fourth sentence."
    Sentence(4) = "This is the fifth sentence."
    Sentence(5) = "This is the sixth sentence."
    Sentence(6) = "This is the seventh sentence."
    Sentence(7) = "This is the eighth sentence."
    Sentence(8) = "This is the ninth sentence."

    For Each aCtl In Controls
        If aCtl.Name Like "CheckBox*" And aCtl = True Then
            sText = GetNum(aCtl.Name)
            sResult = Concat(sResult, CStr(Sentence(Val(sText) - 1)))
        End If
    Next aCtl
    'MsgBox sResult
    FillBM "BookMark_Name", sResult
    Unload Me
End Sub

Private Function Concat(s1 As String, s2 As String, Optional sJoin As String = vbCr)
  If s1 = "" Then
    Concat = s2
  ElseIf s2 = "" Then
    Concat = s1
  Else
    Concat = s1 & sJoin & s2
  End If
End Function

Private Function GetNum(sText As String) As String
Dim i As Integer
    For i = 1 To Len(sText)
        If Mid(sText, i, 1) >= "0" And Mid(sText, i, 1) <= "9" Then
            GetNum = GetNum + Mid(sText, i, 1)
        End If
    Next
lbl_Exit:
    Exit Function
End Function

gmaxey · #5 01-17-2020, 09:07 AM

Andrew, Graham

I'm Johnny come late here, but it seems a ten pound hammer is being used to drive a 6 penny nail. Not saying the discussion about control order is correct or not complicated.

First of all for the user. Using bookmarks for this purpose in the 21st century is like using a horsewhip to accelerate a Ferrari and based on your description, paragraphs (even if they are just one sentence paragraphs) seems to be a better term for what you are dealing with. Assuming that each line in your example is a paragraph I would suggest one of two methods both utilizing a richtext content controls in the document.

The both are adaptations of what you have already seen here. For the first, insert a richtext content control in your document and title it "Content" In your userform insert 9 checkboxes named Checkbox1 through Checkbox9. With a commandbutton1, run this code:

Code:

Option Explicit
Private Sub CommandButton1_Click()
Dim Sentence(8) As Variant
Dim oCtrl As Control
Dim strText As String
Dim lngIndex As Long
  Sentence(0) = "This is the first sentence."
  Sentence(1) = "This is the second sentence."
  Sentence(2) = "This is the third sentence."
  Sentence(3) = "This is the fourth sentence."
  Sentence(4) = "This is the fifth sentence."
  Sentence(5) = "This is the sixth sentence."
  Sentence(6) = "This is the seventh sentence."
  Sentence(7) = "This is the eighth sentence."
  Sentence(8) = "This is the ninth sentence."
  For lngIndex = 1 To 9
    Set oCtrl = Controls("Checkbox" & lngIndex)
    If oCtrl Then
      If strText = vbNullString Then
        strText = Sentence(lngIndex - 1)
      Else
        strText = strText & vbCr & Sentence(lngIndex - 1)
      End If
    End If
  Next lngIndex
  ActiveDocument.SelectContentControlsByTitle("Content").Item(1).Range.Text = strText
  Hide
lbl_Exit:
  Exit Sub
End Sub

For the second, insert a richtext contentcontrol in your document titled "Content Definded". Type in your nine statements (paragraphs). Select all of the content (except the last paragraph mark) and create a buildingblock named "Content" Add a commandbutton2 to your form and Run this code:

Code:

Content"Private Sub CommandButton2_Click()
Dim oCC As ContentControl
Dim Sentence() As String
Dim lngIndex As Long
Dim oCtrl As Object
Dim strText As String
Dim oTmp As Template
  Set oCC = ActiveDocument.SelectContentControlsByTitle("Content Defined").Item(1)
  Set oTmp = ThisDocument.AttachedTemplate
  oTmp.BuildingBlockEntries("Content").Insert oCC.Range, True
  Sentence = Split(oCC.Range.Text, Chr(13))
  For lngIndex = 1 To 9
    Set oCtrl = Controls("Checkbox" & lngIndex)
    If oCtrl Then
      If strText = vbNullString Then
        strText = Sentence(lngIndex - 1)
      Else
        strText = strText & vbCr & Sentence(lngIndex - 1)
      End If
    End If
  Next lngIndex
  ActiveDocument.SelectContentControlsByTitle("Content Defined").Item(1).Range.Text = strText
  Hide
lbl_Exit:
  Exit Sub
End Sub

RevoMedia · #6 01-21-2020, 02:51 AM

Thnx for all the replies!

I need to be clearer.

This is a report that sends a customer of mine to his own customers. The report has 14 chapters, each with a number of paragraphs. Each report has the same standard phrases inside the paragraphs, up to 30 phrases, depending on whether they apply to the customer that it is about.

Now my client would like to have check boxes in his report with which he can select the standard sentences that apply to his client per paragraph, and with a command button everything must be placed in the right place in the report.

gmaxey · #7 01-21-2020, 07:38 AM

That is no clearer than a mud puddle. You start by talking about 9 sentences. Now you are talking about 14 chapters and 30 phrases. Regardless what you ultimately want to achieve, bookmarks are unlikely the ideal way of going about it.

RevoMedia · #8 01-21-2020, 08:04 AM

Correct Greg,
Sorry for that.

I talked about the 9 sentences as an example, but I realized that wasn't clear.

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Field Code: Show a value only if bookmark is not empty	Cosmo	Word	7	08-24-2018 01:46 PM
Removing spaces in activedocument after empty bookmarks	faustino909	Word VBA	2	08-03-2018 01:34 PM
VBA, Place Sentence in its own Line	ilcaa72	Word VBA	6	04-28-2017 07:01 AM
Trying to read CSV file and place values into word bookmarks	Philip1	Word VBA	5	10-27-2016 12:37 AM
Excel vba to check to check if two columns are empty	subspace3	Excel Programming	5	07-09-2015 04:45 PM