I think i'm a few steps closer, and using a Combobox and Content Controls is a better approach over a userform. I say that as I should only need to run the macro once to read the data from excel and create the combobox. I 'think' if I used a userform, I would need to open the excel file each time to when the macro was ran.
Now, with the content controls, and another great post by MVP Greg Maxey here on
Content Controls Magic Dropdowns I think I'm step closer. I can now with the use of the ContentControlOnExit event display the linked information. Its just showing properly like a field code.
Am I on the right track?
Code:
Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim lngIndex As Long
Dim strValue As String
Dim MyLink As String
Select Case CC.Title
Case "ChartLinks"
If CC.ShowingPlaceholderText Then Exit Sub
With CC
For lngIndex = 2 To .DropdownListEntries.Count
If .DropdownListEntries(lngIndex).Text = .Range.Text Then
strValue = .DropdownListEntries(lngIndex).Value
.Type = wdContentControlText
.Range.Text = strValue
.Type = wdContentControlDropdownList
Exit For
'How do I get the value to show as a OLE link?
'strValue = "LINK Excel.SheetMacroEnabled.12 ""C:\\Data\\SampleWPS.xlsm"" ""Cover Charts!Charts_Cover_Background"" \p"
'Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:=strValue, PreserveFormatting:=False
End If
Next lngIndex
End With
Case Else
End Select
lbl_Exit:
Exit Sub
End Sub