Hi ahw,
IMHO, populating a dropdown with 600 entries is somewhat excessive. That said, you would need to use a userform or a dropdown content control for this. The following macro populates a dropdown content control with whatever is in column A in "Sheet1" in the nominated workbook. You can change both the workbook and worksheet references via the StrWkBkNm and StrWkShtNm variables, respectively. The column and row references are managed in the 'For i = 1 To LRow ... Next' loop. The macro also assumes you want to update the first content control in the document having the title 'ID'. You can change the title to something else. Note that the code shows how you can populate both the content control's 'Display name' and 'Value'.
Code:
Sub Document_Open()
Application.ScreenUpdating = False
'Note: A VBA Reference to the Excel Object Model is required, via Tools|References
Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook
Dim StrWkBkNm As String, StrWkShtNm As String, LRow As Long, i As Long
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls"
StrWkShtNm = "Sheet1"
If Dir(StrWkBkNm) = "" Then
MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation
Exit Sub
End If
With xlApp
'Hide our Excel session
.Visible = False
' Open the workbook
Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, ReadOnly:=True, AddToMRU:=False)
' Process the workbook.
With xlWkBk
With .Worksheets(StrWkShtNm)
' Find the last-used row in column A.
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
' Populate the content control titled 'ID', with Column A for the 'ID' as the
' content control Text and the values from columns B-E as the content control
' value, using a "|" separator
ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Clear
For i = 1 To LRow
ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Add _
Text:=Trim(.Range("A" & i))
'or, for example, to add the contents of column B to the content control's 'value':
'ActiveDocument.SelectContentControlsByTitle("ID")(1).DropdownListEntries.Add _
Text:=Trim(.Range("A" & i)), Value:=Trim(.Range("B" & i))
Next
End With
.Close False
End With
.Quit
End With
' Release Excel object memory
Set xlWkBk = Nothing: Set xlApp = Nothing
Application.ScreenUpdating = True
End Sub
If you use the code to populate the content control's 'value', you can then supplement the above code with a content control on-exit macro to populate a dependent content control with that 'value'.
Note: For the above code to work, every Dropdown List Entry and every Dropdown List Value must be unique.
Do be aware that the above code will re-populate the dropdown every time you open the document, wiping out whatever you'd previously selected. To prevent that, change the macro's name from 'Document_Open' to 'Document_New' and add it to the document's template. You may need to create a specific template for this document. You can do that just by saving it as a template (once you've made the code change but before you've made any selections or other edits that you don't want to appear in every new version of this document). Alternatively, you could rename the macro (e.g. UpdateDropDown) so that it will only run on demand (e.g. via Alt-F8>UpdateDropDown>OK).
For PC macro installation & usage instructions, see:
Installing Macros
For Mac macro installation & usage instructions, see:
Word:mac - Install a Macro