![]() |
|
![]() |
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
![]()
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! |
#2
|
||||
|
||||
![]()
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 |
#3
|
||||
|
||||
![]()
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 |
#4
|
||||
|
||||
![]()
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 |
#5
|
|||
|
|||
![]()
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 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 |
#6
|
|||
|
|||
![]()
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. |
#7
|
|||
|
|||
![]()
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.
|
#8
|
|||
|
|||
![]()
Correct Greg,
Sorry for that. I talked about the 9 sentences as an example, but I realized that wasn't clear. |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
Cosmo | Word | 7 | 08-24-2018 01:46 PM |
![]() |
faustino909 | Word VBA | 2 | 08-03-2018 01:34 PM |
![]() |
ilcaa72 | Word VBA | 6 | 04-28-2017 07:01 AM |
![]() |
Philip1 | Word VBA | 5 | 10-27-2016 12:37 AM |
![]() |
subspace3 | Excel Programming | 5 | 07-09-2015 04:45 PM |