Create UserForm ComboBox or ContentControl

ptmuldoon · #2 01-13-2015, 07:43 PM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA Code in a UserForm module to delete a Command Button which opens the userform	Simoninparis	Word VBA	2	09-21-2014 03:50 AM
Is it possible to take an input from a UserForm in one document to a UserForm in a do	BoringDavid	Word VBA	5	05-09-2014 09:08 AM
Trying to Reference a ContentControl Using DocProperty	SuzeG	Word VBA	4	12-17-2013 03:40 PM
Activate/ select ContentControl	czsti	Word VBA	1	08-14-2013 08:32 AM
ComboBox ListIndex = -1 even though it does NOT = -1	Joe Patrick	Word VBA	0	08-03-2011 08:34 AM