# Word VBA (Adding Data into a Content Control Combobox)



## scottie_72 (Feb 2, 2016)

Hi There...
I wonder if anybody could please help? I'm trying to populate a combo box in a template (Which I think I've done!) but dependent on the selection made I'd like the a textbox to be populated with certain text.


I think I may be over complicating things - I would be extremely grateful for any advice (as I'm still a novice)

http://1drv.ms/1nKClUK


----------



## Macropod (Feb 3, 2016)

Why are you both adding the content controls and populating them with code? Since this is a template, you should add both content controls and populate the dropdown manually before saving the template. That requires no code and makes the template much easier to maintain. You should also use a dropdown list content control rather than a combobox, as the latter allows users to input their own text, for which there'd be no payroll output.

The dropdown could be given the display names 'Anne' & 'Barbara', with the  values '23PO78' and '23PO21', respectively.

As for generating the payroll output, that could be done with a ContentControlOnExit macro coded as:

```
Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)
Dim i As Long, StrTxt As String
With CCtrl
  If .Title = "Team Members" Then
    If .Range.Text = .PlaceholderText Then
      StrTxt = ""
    Else
      For i = 1 To .DropdownListEntries.Count
        If .DropdownListEntries(i).Text = .Range.Text Then
          StrTxt = .DropdownListEntries(i).Value
          Exit For
        End If
      Next
    End If
    With ActiveDocument.SelectContentControlsByTitle("Payroll")(1)
      .LockContents = False
      .Range.Text = StrTxt
      .LockContents = True
    End With
  End If
End With
End Sub
```
added to the template's 'ThisDocument' code module and where the dropdown is titled "Team Members" and the output text control is titled "Payroll".


----------



## scottie_72 (Feb 4, 2016)

Hi Macropod...

If I'm completely honest, it was a spur of the moment task that I set myself and I just Googled it and tried the first thing that seemed to fit?

Your code seems really good and but I must be doing something wrong? As I have added the dropdown list control added both names, but its not working! I don't want to sound really thick but I'm afraid I'm going to have to.... where would I enter the Payroll number?

Again Thank you for taking the time to have a look at this for me.


----------



## Macropod (Feb 4, 2016)

When you add an item to a content control dropdown, you get options for inputting the Display Name and a Value. Using your data as an example, the Display Names are 'Anne' & 'Barbara' and their Values are '23PO78' & '23PO21', respectively.


----------



## scottie_72 (Feb 12, 2016)

So so sorry for the late reply... Thank you for that info greatly appreciated  

I've had another brain storm - if I had a list of information in Excel (Name, Payroll) could I link that to content control text boxes on a word document?

Anne


----------



## Macropod (Feb 12, 2016)

Sure! The following macro populates a dropdown content control with whatever is in column A in "Sheet1" in the nominated workbook. You can change both the workbook and worksheet references via the StrWkBkNm and StrWkSht variables, respectively. The column and row references are managed in the 'For i = 1 To iDataRow ... Next' loop. Note that the code shows how you can populate both the content control's 'Display name' and 'Value'.

```
Sub ContentControlPopulate()
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
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls"
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,
    ActiveDocument.ContentControls(1).DropdownListEntries.Clear
    For i = 1 To iDataRow
      ActiveDocument.ContentControls(1).DropdownListEntries.Add Trim(.Range("A" & i))
      'To add the content from Column B as the entry's Value, uncomment the next line
      ActiveDocument.ContentControls(1).DropdownListEntries(i).Value = Trim(.Range("B" & i))
    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
```
If you add the code to the document template's 'ThisDocument' code module and rename it from 'Sub ContentControlPopulate()' to 'Private Document_New()', the current Excel data will be added to any new document based on that template.


----------



## scottie_72 (Feb 12, 2016)

Wow that was fast!!... I'll have a play with that this weekend 

*THANK YOU!
*(I Wish I had your knowledge!..... Got to keep practicing)

Anne


----------



## rlsoccer6 (Oct 5, 2016)

Hi All - can someone please explain to me how to insert the below macro into Visual Basic? I'm very new to VBA, therefore, when I copy and paste into Visual basic and hit run nothing happens... Thanks

Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)
Dim i As Long, StrTxt As String
With CCtrl
  If .Title = "Team Members" Then
    If .Range.Text = .PlaceholderText Then
      StrTxt = ""
    Else
      For i = 1 To .DropdownListEntries.Count
        If .DropdownListEntries(i).Text = .Range.Text Then
          StrTxt = .DropdownListEntries(i).Value
          Exit For
        End If
      Next
    End If
    With ActiveDocument.SelectContentControlsByTitle("Payroll")(1)
      .LockContents = False
      .Range.Text = StrTxt
      .LockContents = True
    End With
  End If
End With
End Sub


----------



## Macropod (Oct 11, 2016)

rlsoccer6 said:


> when I copy and paste into Visual basic and hit run nothing happens


That's because it's a ContentControlOnExit macro that only runs when you exit a Content Control titled 'Team Members'.


----------

