Where to go from here?

macropod · #1 03-27-2016, 03:02 PM

Well, for starters, your code is nothing like that found in any of the links I posted. The link under the heading 'Dropdown Content Control Population from Excel' above is all you need to populate a dropdown content control's text and the corresponding values from Excel. The other links mostly show how you might use content controls for other kinds of processing.

highrise955 · #2 03-27-2016, 03:20 PM

Quote:

Originally Posted by macropod

Well, for starters, your code is nothing like that found in any of the links I posted. The link under the heading 'Dropdown Content Control Population from Excel' above is all you need to populate a dropdown content control's text and the corresponding values from Excel. The other links mostly show how you might use content controls for other kinds of processing.

Except I can't make heads or tails what's going on in all that code. All I want to do is populate a simple listbox or combobox with the values from an excel spreadsheet when the document is opened. It seems like it would be so simple but every video or tutorial I find never quite hits the mark.

macropod · #3 03-27-2016, 05:19 PM

Quote:

Originally Posted by highrise955

Except I can't make heads or tails what's going on in all that code. All I want to do is populate a simple listbox or combobox with the values from an excel spreadsheet when the document is opened. It seems like it would be so simple but every video or tutorial I find never quite hits the mark.

Which is precisely what the code does. All you need to do is point the code to your workbook & worksheet, by adjusting the lines:
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls"
StrWkShtNm = "Sheet1"
As coded, the macro expects the dropdown to be populated to be the first one in the document, but I'm sure you can see where you can change that in the code.

highrise955 · #4 03-29-2016, 01:10 AM

Quote:

Originally Posted by macropod

Which is precisely what the code does. All you need to do is point the code to your workbook & worksheet, by adjusting the lines:
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls"
StrWkShtNm = "Sheet1"
As coded, the macro expects the dropdown to be populated to be the first one in the document, but I'm sure you can see where you can change that in the code.

OK, I think I figured it out. I got the code to work but I have one final question...

Code:

With xlWkBk.Worksheets(StrWkSht)
    ' Find the last-used row in column A.
    LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    ' Populate the content control,
    ActiveDocument.ContentControls(4).DropdownListEntries.Clear
    For i = 1 To LRow
      ActiveDocument.ContentControls(4).DropdownListEntries.Add Text:=Trim(.Range("A" & i)), Value:=Trim(.Range("B" & i))
    Next
  End With

How do I exclude Row 1 from the worksheet when it populates the DropdownList? I'm using that row as the column titles. Better yet, can I specify a specific row to start the .Range at?

Your input (and patience) is very much appreciated.

macropod · #5 03-29-2016, 02:19 AM

Quote:

Originally Posted by highrise955

OK, I think I figured it out. I got the code to work but I have one final question...
...

How do I exclude Row 1 from the worksheet when it populates the DropdownList? I'm using that row as the column titles. Better yet, can I specify a specific row to start the .Range at?

That's as easy as:
For i = 2 To LRow