Where to go from here?

macropod · #1 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 · #2 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 · #3 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