I am making a form template for my work.
I want to add drop-down list that import the data from excel cells.
The excel sheet contains about 600 rows, in each row (name) + (Number).
So, when I edit the form I just need to press the list which will import the list and I choose one record.


Thanks a lot in advance

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'.
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

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

This code will save me loads of time! I've tried using it in Word 2010 but I get the following error on running the macro:
Run-time error '6189':

This property can only be used with dropdown list or combo box content controls.

I have selected my DropDown List Content Control in Word that I want to put the values into, but it's not having it. Can I call it by code? The title of my dropdown list is Cause if that helps.

---Brerbunny

As coded, the macro assumes you want to update the first content control in the document having the title 'ID'. Evidently the one you want to update either lacks the title or isn't the first with that title. An alternative approach is to refer to the content control by its relative position in the document, using code like:
ActiveDocument.ContentControls(1)
replacing the '1' with whatever number corresponds with your dropdown content control. Selecting the content control makes no difference.

If you want to populate another content control with data based on your selection from the dropdown, you could adapt the macro in the attachment to post: https://www.msofficeforums.com/word-vba/16498-multiple-entries-dropdown-lists.html. With a little work, that code could even be modified to output different data to each of a plethora of other content controls.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Is there any way I can find out which number my dropdown list is from Word? I thought that I only had dropdown lists as Content Control items, but if I'm getting that error there must be another Content Control item I'm not aware of.
Thanks!

Every content control in the document has a unique id. You can determine the ID by selecting the CC title tab and running

Debug.Print Selection.Range.ContentControls(1).ID
'or
MsgBox Selection.Range.ContentControls(1).ID

You can then act on that CC with code using:

ActiveDocument.ContentControls("XXXXXXXXX").....

where "XXXXXXXX" represents the unique id number

__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/

Hi! This is just what I was looking for.
Can you please just explain where and how I should enter this code? I tried the Visual Basic button on the Developer Tab, saved it and then clicked to run the macro. But it didn't work...

Any help is very much appreciated.

Thank you.

If you are asking about:

You can run either from the VB Immediate window.

__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/

Hi, @gmaxey,
I was asking about the very first code on this thread (I'm sorry if I didn't place my question correctly).

I have a word template file with just one dropdown list created. I named the dropdown list as "Clients" and it has no options entered yet. I wanted to fill it with a list of clients that I have on column A of Sheet1 on an Excel file called "Clients List".

I created a macro on Word called Dropdownlist and I pasted the suggested code there. I changed the location for the StrWkBkNm file.

When I run the macro I get the error message: "Run-time error '1004': Application-defined or object-defined error". The file does exist and column A of sheet1 has data from A1 down to A106...

Do you have a suggestion about how to fix this?
Would this have to do with something called "declarations" that I saw somewhere?

Thank you in advance for any tip.

Have you done all that the instructions in post #2 say you need to do? Did you
• set a reference to Excel?
• edit the StrWkBkNm line to point to your workbook-
StrWkBkNm = "C:\Users\" & Environ("Username") & "\Documents\Workbook Name.xls"
• edit the StrWkShtNm line to point to your worksheet-
StrWkShtNm = "Sheet1"

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you, @macropod, for getting back to me. I can't remember now what the specific issue was, but I already got this functioning beautifully. And I also had a button on the document that can be double-clicked every time we need to update the drop-down list. Thank you once more.

Thanks, macropod, your macro works well. I am revamping an old form and trying to make it easier to manage with the list data in an Excel file. I am using the macro with this variation:

ActiveDocument.ContentControls(1)

How could I adapt this so that all my dropdown lists are updated, rather than call them individually, I call them all as a group or collective? I hope that makes sense.

Thanks.

Liz

OK, sorry for the post without doing more research...was able to fix my "typo" which made it so that the drop list were not populating appropriately.

ActiveDocument.SelectContentControlsByTitle("use_y our_own_unique_title")(1) works for all the drop lists.

Cheers!

First of all, thanks for this thread, it is proving useful to me even years after the original post.

I am working on something similar, but I want the trimmed values from column B to be inserted into a plain text content control instead of in the Value field of the drop down list. I have been trying to reference the title of the plain text control and assign the trimmed value to it, but to no avail. How can this be achieved?

The code in post #2 provides for the secondary data to be stored in the dropdown so that, once the appropriate selection is made, the relevant secondary item can be output to a subsidiary content control using code like that referenced in the link in post #4. I doubt you'd want multiple items being written directly to your plain text content control.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]