Microsoft Office Forums - View Single Post - Let bookmark check if there are empty bookmarks before and place sentence there?

gmayor · 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