Using VBA to map Rich text CC to a Dropdown list in a Combo Box

Aarish Khawar · #1 02-14-2023, 05:56 PM

Hello All,

I should preface this with the fact that I am quite new to VBA and Macros and am still learning the basics.

I'm trying to build a form within a table that contains a drop-down list (Combo Box - as I want the user to have the option of entering their own value if none of the options meet their requirements) of options that I want the user to be able to select from. Once the user has selected an option, I want a custom paragraph to appear within a rich text content control (RTCC) box which is located later in the document.

From what I understand, the best way to do this is to have each custom paragraph in a separate RTCC with its own "title". So that once mapped to the drop-down list, the specific RTCC is the only one visible when an option from the drop-down list is selected. I would prefer that none of the RTCCs are visible if no option has yet been selected purely for aesthetic reasons but I am willing to let this slide. Also, it would be ideal if the custom paragraphs updated "OnChange", however "OnExit" is good too.

So far, I've been able to add titles to each RTCC and the following code down:

Sub ShowParagraph()
Dim sParagraph As String
sParagraph = ActiveDocument.ContentControls("Combo Box").Range.Text
Select Case sParagraph
Case "BCA_DCC"
ActiveDocument.ContentControls("BCA_DCC.TXT").Rang e.Text = "This is paragraph 1"
Case "BCA_NODCC"
ActiveDocument.ContentControls("BCA_NODCC.TXT").Ra nge.Text = "This is paragraph 2"
Case "SOA_DCC"
ActiveDocument.ContentControls("SOA_DCC.TXT").Rang e.Text = "This is paragraph 3"
Case "SOA_NODCC"
ActiveDocument.ContentControls("SOA_NODCC.TXT").Ra nge.Text = "This is paragraph 4"
End Select
End Sub

Where values e.g., "BCA_DCC" are options in the drop-down list and "BCA_DCC.TXT" are titles for their corresponding RTCC containing custom paragraphs.

Note: I am using the latest version of Word.

Any feedback would be highly appreciated!

Best,
Aarish

gmayor · #2 02-14-2023, 10:48 PM

Hmmm. I can foresee a couple of issues here.

1. As you can only make one selection from a combo box, the only justification for having four rich text controls would be if the four related paragraphs are scattered around the document. Otherwise you could put the related values in just one control.
2. As you point out, a combo box allows for user entry. What do you want entered if the user enters in a non-standard text?
3. You can make the unused content controls disappear by filling them with a zero length space - ChrW(8203)
The following code Will do what you describe. It goes in the ThisDocument module

Code:

Option Explicit

Private OCC As ContentControl
Private sNul  As String

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim sCC As String
    sNul = ChrW(8203)
    If ContentControl.Title = "Combo1" Then
        If ContentControl.ShowingPlaceholderText = False Then
            Select Case Trim(ContentControl.Range.Text)
                Case "BCA_DCC"
                    For Each OCC In ActiveDocument.ContentControls
                        If OCC.Title = "BCA_DCC.TXT" Then
                            OCC.Range.Text = "Paragraph 1"
                        Else
                            If OCC.Type = wdContentControlRichText Then
                                OCC.Range.Text = sNul
                            End If
                        End If
                    Next OCC
                Case "BCA_NODCC"
                    For Each OCC In ActiveDocument.ContentControls
                        If OCC.Title = "BCA_NODCC.TXT" Then
                            OCC.Range.Text = "Paragraph 2"
                        Else
                            If OCC.Type = wdContentControlRichText Then
                                OCC.Range.Text = sNul
                            End If
                        End If
                    Next OCC
                Case "SOA_DCC"
                    For Each OCC In ActiveDocument.ContentControls
                        If OCC.Title = "SOA_DCC.TXT" Then
                            OCC.Range.Text = "Paragraph 3"
                        Else
                            If OCC.Type = wdContentControlRichText Then
                                OCC.Range.Text = sNul
                            End If
                        End If
                    Next OCC
                Case "SOA_NODCC"
                    For Each OCC In ActiveDocument.ContentControls
                        If OCC.Title = "SOA_NODCC.TXT" Then
                            OCC.Range.Text = "Paragraph 4"
                        Else
                            If OCC.Type = wdContentControlRichText Then
                                OCC.Range.Text = sNul
                            End If
                        End If
                    Next OCC
                Case Else
                    For Each OCC In ActiveDocument.ContentControls
                        If OCC.Type = wdContentControlRichText Then
                            OCC.Range.Text = sNul
                        End If
                    Next OCC
            End Select
        Else
            For Each OCC In ActiveDocument.ContentControls
                If OCC.Type = wdContentControlRichText Then
                    OCC.Range.Text = sNul
                End If
            Next OCC
        End If
    End If
End Sub

If the document is a template, which it almost certainly should be, you can hide the rich text controls with

Code:

Private Sub Document_New()
    sNul = ChrW(8203)
    For Each OCC In ActiveDocument.ContentControls
        If OCC.Type = wdContentControlRichText Then
            OCC.Range.Text = sNul
        End If
    Next OCC
End Sub

in the same module.
You will also need a macro to display them again for the purpose of editing (or you could use Insert Content Control Add-In

Code:

Sub ShowCCs()
Dim oCC As ContentControl
    For Each oCC In ActiveDocument.ContentControls
        oCC.Range.Text = ""
    Next oCC
End Sub

Aarish Khawar · #3 02-15-2023, 06:29 PM

Hi Graham,

I cannot thank you enough for this, this is in fact what I had in mind.

I've addressed some of your comments below:

1. As you can only make one selection from a combo box, the only justification for having four rich text controls would be if the four related paragraphs are scattered around the document. Otherwise, you could put the related values in just one control.

1. I read somewhere that having separate controls would be the way to go about accomplishing this but now that you've mentioned it, having all of the paragraphs in one control makes way more sense as they are located within the same cell. Although for the life of me, I have no idea how to write the syntax for that. (Btw yes it is a template)

2. As you point out, a combo box allows for user entry. What do you want entered if the user enters in a non-standard text?

2. If the user enters a non-standard text, it would be ideal if the control with the paragraph remains blank and allows the user to input their own paragraph.

___

Side note: Would it also be possible to map multiple controls to a selected option within the initial combo box? If so, how would I go about doing that?

Thank you once again for this. Any further feedback would be much appreciated!

Best
Aarish

gmayor · #4 02-15-2023, 10:15 PM

If you want to write the associated text to one control, the code is simpler

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim oRng As Range
    sNul = ChrW(8203)
    Set oRng = ActiveDocument.SelectContentControlsByTitle("Text 1").Item(1).Range
    If ContentControl.Title = "Combo1" Then
        If ContentControl.ShowingPlaceholderText = False Then
            Select Case Trim(ContentControl.Range.Text)
                Case "BCA_DCC"
                    oRng.Text = "Paragraph 1" & vbCr & "Paragraph 3"
                Case "BCA_NODCC"
                    oRng.Text = "Paragraph 2"
                Case "SOA_DCC"
                    oRng.Text = "Paragraph 3"
                Case "SOA_NODCC"
                    oRng.Text = "Paragraph 4"
                Case Else
                    oRng.Text = ""
            End Select
        End If
    End If
End Sub

If you want to write different texts to two or more controls then you can do that also

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim oRng1 As Range, oRng2 As Range
    sNul = ChrW(8203)
    Set oRng1 = ActiveDocument.SelectContentControlsByTitle("Text 1").Item(1).Range
    Set oRng2 = ActiveDocument.SelectContentControlsByTitle("Text 2").Item(1).Range

    If ContentControl.Title = "Combo1" Then
        If ContentControl.ShowingPlaceholderText = False Then
            Select Case Trim(ContentControl.Range.Text)
                Case "BCA_DCC"
                    oRng1.Text = "Paragraph 1" & vbCr & "Paragraph 3"
                    oRng2.Text = sNul
                Case "BCA_NODCC"
                    oRng1.Text = "Paragraph 2"
                    oRng2.Text = "Paragraph 4"
                Case "SOA_DCC"
                    oRng1.Text = "Paragraph 3"
                    oRng2.Text = sNul
                Case "SOA_NODCC"
                    oRng1.Text = "Paragraph 4"
                    oRng2.Text = sNul
                Case Else
                    oRng1.Text = ""
                    oRng2.Text = ""
            End Select
        End If
    End If
End Sub

What you can't do is make multiple selections in a combo box. If you want to make multiple selections you need a userform with a list box. You can find how to do that on my web site.

Aarish Khawar · #5 02-26-2023, 02:58 PM

I learned so much from this. Thank you! This works beautifully!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Link text field to dropdown list	borus	Word	3	08-16-2023 05:36 AM
Text based on selection of dropdown list	cloud67	Word VBA	2	08-09-2019 06:46 AM
Copy Formatted Text in one Rich Text Content Control to another Rich Text Content Control	Haygordon	Word	1	04-05-2019 05:43 AM
Mutiple drop down list connected to rich text in one document	nikesh01	Word VBA	1	02-05-2016 01:06 PM
VBA: How to place dropdown list next to text	YigalB	Word VBA	0	08-11-2013 01:48 PM