Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-14-2020, 12:42 AM
RevoMedia RevoMedia is offline Let bookmark check if there are empty bookmarks before and place sentence there? Windows 10 Let bookmark check if there are empty bookmarks before and place sentence there? Office 2016
Novice
Let bookmark check if there are empty bookmarks before and place sentence there?
 
Join Date: Jan 2020
Location: Doetinchem, NL
Posts: 1
RevoMedia is on a distinguished road
Default Let bookmark check if there are empty bookmarks before and place sentence there?

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!
Reply With Quote
  #2  
Old 01-14-2020, 02:05 AM
gmayor's Avatar
gmayor gmayor is offline Let bookmark check if there are empty bookmarks before and place sentence there? Windows 10 Let bookmark check if there are empty bookmarks before and place sentence there? Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,066
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 01-14-2020, 03:37 PM
Guessed's Avatar
Guessed Guessed is online now Let bookmark check if there are empty bookmarks before and place sentence there? Windows 10 Let bookmark check if there are empty bookmarks before and place sentence there? Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,413
Guessed is a jewel in the roughGuessed is a jewel in the roughGuessed is a jewel in the roughGuessed is a jewel in the rough
Default

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
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #4  
Old 01-14-2020, 11:36 PM
gmayor's Avatar
gmayor gmayor is offline Let bookmark check if there are empty bookmarks before and place sentence there? Windows 10 Let bookmark check if there are empty bookmarks before and place sentence there? Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,066
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 01-17-2020, 09:07 AM
gmaxey gmaxey is offline Let bookmark check if there are empty bookmarks before and place sentence there? Windows 10 Let bookmark check if there are empty bookmarks before and place sentence there? Office 2016
Word MVP 2003-2009
 
Join Date: May 2010
Location: Marble, NC
Posts: 984
gmaxey will become famous soon enoughgmaxey will become famous soon enough
Default

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
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 04:10 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft