Create UserForm ComboBox or ContentControl

ptmuldoon · #1 01-13-2015, 02:22 PM

I've been struggling with the best way for a user to be able to press a button/macro, etc then choose from a selection of charts to add to their document. I think I moved away from using building blocks to either a UserForm or Content Control.

In my Excel file, I have created a worksheet that has 2 columns. A Chart Name and the LINK code of the chart.

From Macropod, I found this post on creating a Content Control how-import-list-excel-into-drop-down But for some reason, when I when chose an Item, I am not getting the Value to show in the word document although I see a different value (ie, column B) when viewing the properties.

and then I also found this post by MVP Greg Maxey on setting up a Userform list from the excel data populate_userform_listbox_or_combobox. But again, I'm still struggling on getting the dropdown list to show a Chart Name/Description, and then input the link code.

I can post more sample code. But before doing so. Which would be the better approach to work with.

And to help, In word, I can currently open the excel file, get the link and add it with the below. Now I'm trying to create a listing of all of the charts/links

Code:

'    Code above to get Excel File and Chart.  Using late Binding so user
'    does not need to set reference in Word.
'
'    With objSht
'        ChartLink = .Range("C2")
'        MsgBox ChartLink
'    End With
'
'    C2 Cell Value is = LINK  Excel.SheetMacroEnabled.12 "C:\\Data\\SampleWPs.xlsm" "Cover Charts!Charts_Cover_Main" \p
'    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:=ChartLink, PreserveFormatting:=False

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

macropod · #3 01-13-2015, 09:13 PM

The macro I posted in the link you gave is only for populating the dropdown; not for doing anything with whatever might be selected. If you select an item from the populated dropdown, that item should be displayed.

If you want to work with (e.g. display) something else from the workbook according to the item selected, you should add whatever that is to the dropdown as the item Value when populating the content control. For example:

Code:

ActiveDocument.ContentControls(1).DropdownListEntries.Add Text:=Trim(.Range("A" & i)), Value:=Trim(.Range("B" & i))

Then, when you exit the content control after making your selection, use a Document_ContentControlOnExit macro to retrieve the value and put it somewhere else. For code to do that, see: https://www.msofficeforums.com/word-...html#post46903. You already seem to be doing much the same.

For what you're trying to do, if there is always only one chart to display in a given document, could have the field code already in the document and just change the chart reference (that being what you'd use for the content control Value).

ptmuldoon · #4 01-14-2015, 06:21 AM

Thanks Macropod, you guys are amazing at the time and help you provide.

Yes, I did read further in the post and I am currently including the two columns like you mentioned, only modified for columns B and C like below.

Code:

ActiveDocument.ContentControls(1).DropdownListEntries.Add Trim(.Range("B" & i)), Trim(.Range("C" & i))

And when I view the properties of the CC, I see both the Display Name and Value correctly.

And then learning about the ControlOnExit, the dropdown shows the Display Names, and then changes to the value on Exit of the CC.

But I'm currently stuck at getting that value to actually display as a FieldCode Link Source. I've tried adding the { } to the cell range to no luck and still displays a text.. So believe I need to somehow modify the VBA to insert CC value as link field code?

My Word document currently has about 60 chartlinks in the document. And as user incorrectly deletes them (which they will do!!), I'm trying to provide a way for the user to choose from a list and reinsert a linked chart.

macropod · #5 01-14-2015, 06:27 AM

It's not clear what you're trying to achieve with the Document_ContentControlOnExit macro. Do you intend to replace the content control with the chart, to insert the chart into the content control, or to insert the chart somewhere else?

ptmuldoon · #6 01-14-2015, 07:17 AM

Quote:

Originally Posted by macropod

It's not clear what you're trying to achieve with the Document_ContentControlOnExit macro. Do you intend to replace the content control with the chart, to insert the chart into the content control, or to insert the chart somewhere else?

I'm really open to any idea on achieving the end result, which is a way for the user to have a listing of all charts available and be able to insert a chart into the document. It does not have to be in a Content Control, but I think that is the current best approach to creating the listing of charts available? The macro would only need to run once to generate the listing.

I could possibly (still learning) accomplish with a user form to list all of the Chart Names (ie, column b), andthen have additional code to insert the chart link code (column C). But wouldn't that require to open and read the excel chart each time?

macropod · #7 01-14-2015, 01:01 PM

Sure, the content control is the best place to list the charts, but I wasn't asking about that. I was asking about where you want the selected chart to go and how many charts you're trying to insert into a given document.

ptmuldoon · #8 01-14-2015, 02:07 PM

My word document is currently linking to 63 separate tables in Excel. All of those links are already in the document, and the user can simply press F9 or right click to update the table.

But there will be (and has happened already) times when the user mistakenly deletes the charts from table. And they do have the 'know how' on creating/adding the paste-link table back into the document.

So, the ContentControl should provide a listing of all of the available charts. And the user can then insert a chart back into the document. It does not have to be specifically within the content control.

I think may be better to have it added outside the content control actually in case the user needs to edit/change the link source?

I've created a listing in excel (attached), and I can add the link code in with this basic code for a single link. But creating a full listing is still beyond me.

Note, the 'links' in the excel file all used names ranges to where the actual tables are in the excel file.

Code:

'  Additional code above using late binding..
' 
Set objXL = CreateObject("Excel.Application")   'set reference to the Excel Object
    Set objWkb = objXL.Workbooks.Open(FileName:=prmFileName)  'set reference to the Excel workbook
    Set objSht = objWkb.Sheets(prmSheetName)  'set reference to the Excel Sheet
       
    With objSht
        ChartLink = .Range("C2")
        MsgBox ChartLink
    End With

    Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:=ChartLink, PreserveFormatting:=False

macropod · #9 01-14-2015, 03:07 PM

Quote:

Originally Posted by ptmuldoon

My word document is currently linking to 63 separate tables in Excel. All of those links are already in the document, and the user can simply press F9 or right click to update the table.

But there will be (and has happened already) times when the user mistakenly deletes the charts from table. And they do have the 'know how' on creating/adding the paste-link table back into the document.

Surely, then, the simplest method is to teach them how to do that the conventional way - select & copy the chart in Excel, then use Paste Special with the 'paste link' option in Word, without all the overheads you're building into the process. That also means the user can control where the chart gets pasted, which is much more difficult to achieve when you're using a macro tied to a content control.

ptmuldoon · #10 01-16-2015, 12:34 PM

I haven't given up on this, and just read another post here how someone is using a userform and listbox/combobox to insert the text.

I think with a userform. I may be able to have a listing of charts in Column B in Excel, and when the user selects the item, to insert the information from Column C.

I hope to work more on it tonight after getting my son down for the night.

macropod · #11 01-16-2015, 02:42 PM

There is no real difficulty with inserting the chart - the code for that is quite straightforward. The difficult part is in allowing the user to choose where it will go. Yes, you could use a userform, but how do you propose the user going to get that to run? After all, unlike a content control, it won't exist in the body of the document.

ptmuldoon · #12 01-17-2015, 05:58 PM

Quote:

Originally Posted by macropod

There is no real difficulty with inserting the chart - the code for that is quite straightforward. The difficult part is in allowing the user to choose where it will go. Yes, you could use a userform, but how do you propose the user going to get that to run? After all, unlike a content control, it won't exist in the body of the document.

I may not have mentioned in the past, but I can push a global template/addin to all users machines. So they can run a macro with either a keyboard shortcut or adding to the ribbon (I haven't learned the ribbon part yet though)

I already have a global template / addin file for all machines. So I can just update that file and push it out. But of course won't do that till after running some test locally.

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