Using content control drop down to pull variables from Excel and change conditional text

Question

I have a template, that I would like to use to create about 20 different documents, each for a different 'Journal' (ie. Journal A, Journal B, Journal C)...where the text is highly conditional.

In the title, I have a content control dropdown box that says "Guideline for (JournalName)", where JournalName is a dropdown that contains all the different journal options.

What I would like to happen, is when I choose a specific journal, the text content of the template changes for that specific journal.

I have an excel file that has all the relevant data with column names such as Col A: JournalName, Col B: JournalCode, etc.

I have tried mail merge and found that to be a mess to format, and I would like this to be a bit more user-friendly, so that all one has to do is use the dropdown box to change between journals, or edit the data in the excel file.

Answer 1

Thanks for all your help! I'm replying because I found a different solution, but maybe this will help someone.

If ActiveDocument.Bookmarks("Bookmark1").Empty = True Then

ActiveDocument.Bookmarks("Bookmark1").Select

Selection.Font.Hidden = True

End If

If ActiveDocument.Bookmarks("Bookmark1").Empty = False Then

ActiveDocument.Bookmarks("Bookmark1").Select

Selection.Font.Hidden = False

End If

Maybe not the most elegant solution, but does exactly what I need it to do.

Answer 2

Thanks so much! The first macro works great. For the second part, would these automatically populate depending on the value chosen in the first dropdown (journalName)?

One of my issues is that I have conditional text within conditional text, so I'm not sure if this would work unless I can have nested dropdowns or  nested bookmarks.

For example- certain sections of text are optional (some journals may have it, others may not), and then within the section, if a journal does have that text, there are variable values within it.

Thanks!

Answer 3

Instead of a userform, you could populate a dropdown content control from an Excel data source by adding code like the following to the 'ThisDocument' code module of the document's template, where:

• 'StrWkBkNm' holds the workbook's path & name

• 'StrWkSht' holds the worksheet name and

• 'JournalNames' is the title of the dropdown content control:

Sub Document_New()

Application.ScreenUpdating = True

Dim xlApp As Object, xlWkBk As Object, StrWkBkNm As String, StrWkSht As String

Dim bStrt As Boolean, iDataRow As Long, bFound As Boolean, i As Long, j As Long

Dim CCtrl As ContentControl, StrData As String

StrWkBkNm = "C:\Users" & Environ("Username") & "\Documents\Workbook Name.xlsx"

StrWkSht = "Sheet1"

If Dir(StrWkBkNm) = "" Then

  MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation

  Exit Sub

End If

' Test whether Excel is already running.

On Error Resume Next

bStrt = False ' Flag to record if we start Excel, so we can close it later.

Set xlApp = GetObject(, "Excel.Application")

'Start Excel if it isn't running

If xlApp Is Nothing Then

  Set xlApp = CreateObject("Excel.Application")

  If xlApp Is Nothing Then

    MsgBox "Can't start Excel.", vbExclamation

    Exit Sub

  End If

  ' Record that we've started Excel.

  bStrt = True

End If

On Error GoTo 0

'Check if the workbook is open.

bFound = False

With xlApp

  'Hide our Excel session

  If bStrt = True Then .Visible = False

  For Each xlWkBk In .Workbooks

    If xlWkBk.FullName = StrWkBkNm Then ' It's open

      Set xlWkBk = xlWkBk

      bFound = True

      Exit For

    End If

  Next

  ' If not open by the current user.

  If bFound = False Then

    ' Check if another user has it open.

    If IsFileLocked(StrWkBkNm) = True Then

      ' Report and exit if true

      MsgBox "The Excel workbook is in use." & vbCr & "Please try again later.", vbExclamation, "File in use"

      If bStrt = True Then .Quit

      Exit Sub

    End If

    ' The file is available, so open it.

    Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm)

    If xlWkBk Is Nothing Then

      MsgBox "Cannot open:" & vbCr & StrWkBkNm, vbExclamation

      If bStrt = True Then .Quit

      Exit Sub

    End If

  End If

  ' Process the workbook.

  With xlWkBk.Worksheets(StrWkSht)

    ' Find the last-used row in column A.

    ' Add 1 to get the next row for data-entry.

    iDataRow = .Cells(.Rows.Count, 1).End(-4162).Row ' -4162 = xlUp

    ' Populate the content control,

    Set CCtrl = ActiveDocument.SelectContentControlsByTitle("JournalNames").Item(1)

    CCtrl.DropdownListEntries.Clear

    For i = 1 To iDataRow

      StrData = Trim(.Range("B" & i)) & " " & Trim(.Range("C" & i)) & "|" & _

                Trim(.Range("D" & i)) & "|" & Trim(.Range("E" & i)) & "|" & _

                Trim(.Range("F" & i)) & ", " & Trim(.Range("G" & i)) & " " & _

                Trim(.Range("H" & i)) & "|" & Trim(.Range("I" & i))

        CCtrl.DropdownListEntries.Add Trim(.Range("A" & i))

        CCtrl.DropdownListEntries(i).Value = StrData

    Next

  End With

  If bFound = False Then xlWkBk.Close False

  If bStrt = True Then .Quit

End With

' Release Excel object memory

Set xlWkBk = Nothing: Set xlApp = Nothing

Application.ScreenUpdating = True

End Sub

Function IsFileLocked(strFileName As String) As Boolean

  On Error Resume Next

  Open strFileName For Binary Access Read Write Lock Read Write As #1

  Close #1

  IsFileLocked = Err.Number

  Err.Clear

End Function

As coded, the macro stores the workbook data from column A as the dropdown display text and stores the data from columns B-I as pipe-delimited strings as the corresponding Values. These values can then be used to populate various text content controls in the document, using code like:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)

Dim i As Long, StrOut As String

With ContentControl

  If .Title = "JournalNames" Then

    For i = 1 To .DropdownListEntries.Count

      If .DropdownListEntries(i).Text = .Range.Text Then

        StrOut = .DropdownListEntries(i).Value

        Exit For

      End If

    Next

    With ActiveDocument

      .ContentControls(2).Range.Text = Split(StrOut, "|")(0)

      .ContentControls(3).Range.Text = Split(StrOut, "|")(1)

      .ContentControls(4).Range.Text = Split(StrOut, "|")(2)

      .ContentControls(5).Range.Text = Split(StrOut, "|")(3)

      .ContentControls(6).Range.Text = Split(StrOut, "|")(4)

      .ContentControls(7).Range.Text = Split(StrOut, "|")(5)

      .ContentControls(8).Range.Text = Split(StrOut, "|")(6)

      .ContentControls(9).Range.Text = Split(StrOut, "|")(7)

    End With

  End If

End With

End Sub

If you prefer, bookmarks could be used instead of text content controls for the ouput, using code like:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)

Dim i As Long, StrOut As String

With ContentControl

  If .Title = "JournalNames" Then

    For i = 1 To .DropdownListEntries.Count

      If .DropdownListEntries(i).Text = .Range.Text Then

        StrOut = .DropdownListEntries(i).Value

        Exit For

      End If

    Next

    With ActiveDocument

      Call UpdateBookmark("Bookmark1", Split(StrOut, "|")(0))

      Call UpdateBookmark("Bookmark2", Split(StrOut, "|")(1))

      Call UpdateBookmark("Bookmark3", Split(StrOut, "|")(2))

      Call UpdateBookmark("Bookmark4", Split(StrOut, "|")(3))

      Call UpdateBookmark("Bookmark5", Split(StrOut, "|")(4))

      Call UpdateBookmark("Bookmark6", Split(StrOut, "|")(5))

      Call UpdateBookmark("Bookmark7", Split(StrOut, "|")(6))

      Call UpdateBookmark("Bookmark8", Split(StrOut, "|")(7))

    End With

  End If

End With

End Sub

Sub UpdateBookmark(StrBkMk As String, StrTxt As String)

Dim BkMkRng As Range

With ActiveDocument

  If .Bookmarks.Exists(StrBkMk) Then

    Set BkMkRng = .Bookmarks(StrBkMk).Range

    BkMkRng.Text = StrTxt

    .Bookmarks.Add StrBkMk, BkMkRng

  End If

End With

Set BkMkRng = Nothing

End Sub

Answer 4

See the following pages of Greg Maxey's website :

http://gregmaxey.mvps.org/Create\_and\_employ\_a\_UserForm.htm 

http://gregmaxey.mvps.org/Populate\_UserForm\_ListBox.htm