#1
|
|||
|
|||
Having a Drop-down list in Word referring to an Excel list
Hello, brilliant people!
I wonder if this can be done: I have a Word Template Document with several fields. In one of them, I want to have a drop-down list whose items are on an Excel File called "ClientList" - in Sheet1, column A. This list has more than 100 items. Is it possible to automatically refer this drop-down list to that Excel list so that it is always updated, meaning that if any changes are made to the Excel file, the next time I open the Word template document, the list is complete and up-to-date? I know very little of Macros and VBA but I am willing to learn. If this is something that you think you could teach me how to do, please remember that I am a newbie. Thank you very much in advance for any help provided. |
#2
|
||||
|
||||
You would need VBA to read the Worksheet into a userform list or combo box (or into an array).
The basics of userforms are covered at http://www.gmayor.com/Userform.htm where you will also find an example template. Example code for filling a userform list of combo box is shown at http://www.gmayor.com/Userform_ComboBox.html. Virtually instantaneously, this reads the worksheet into the list and so can be applied any time you open the userform and it will always show the last saved version of the data. Arrays you can discover another day.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Dear @gmayor,
I tried my best to follow your instructions and I even read "Idiot's guide to instal macros". Yet, this isn't working. My fault, of course, for not knowing very basic stuff. Here are a few stupid questions. I hope your patience and kindness are enough to guide me here: Where do I paste the codes from 1st and 3rd boxes that you present at http://gmayor.com/userform_combobox.html ? Should any of these codes be pasted in between something like Sub Dropdownlist() End Function? Do I need to create the Drop-down list first in Word? If I am on a Word Template document with more than one drop-down list, how do I tell the macro which drop-list to fill? I have a few more questions, but if I could get an answer for these ones for now, it would be super fantastic! Thank you once more for your patience. |
#4
|
||||
|
||||
The code is intended to fill a list or combo box on a userform. It is not intended to fill a dropdown list in a document. You can use variations of the same code to fill more than one list or combobox, from the same or different worksheets.
Note that the code inserts the whole worksheet (or range) into the list or combo box, but displays only one selectable column. The other columns are present but hidden, to avoid cluttering the display. If all the information you wish to include in your document is in a single record on the worksheet. The same userform list or combo can be employed to write the selected record to the document, by writing the appropriate column of the box to the relevant part of the document. I would suggest using bookmarks at those document locations and use the code at the end of the first of my linked pages to write the column values to the bookmarks. Code:
Private Sub CommandButton1_Click() If ComboBox1.ListIndex > 0 Then FillBM "BookmarkName1", ComboBox1.Column(1) FillBM "BookmarkName2", ComboBox1.Column(2) 'etc End If Unload Me End Sub The combo or list box goes on the userform and the code in the first of the code boxes on the web page goes in the Initialization code of the userform to populate the list or combo box when the userform is called. e.g. Code:
Private Sub UserForm_Initialize() 'The displayed worksheet column is iColumn - here column 2. Change as required 'Change the workbook path and worksheet name as appropriate 'Change ComboBox1 to the name of the list or combo box on the userform xlFillList ListOrComboBox:=ComboBox1, _ iColumn:=2, _ strWorkbook:="C:\Path\WorkBookName.xlsx", _ strRange:="SheetName", _ RangeIsWorksheet:=True, _ RangeIncludesHeaderRow:=True ComboBox1.ListIndex = -1 End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
Tags |
drop down lists, excel list, word template |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to import list from Excel into drop-down list into word | ahw | Word VBA | 43 | 02-28-2020 08:11 PM |
Microsoft Excel, Drop Down List | Phibir | Excel | 1 | 04-16-2015 01:01 PM |
Populate Word Drop-down list with Excel column then auto fill form fields with Excel data | Faldinio | Word VBA | 7 | 10-19-2014 06:03 AM |
(Excel 2007) Need help with colour change when condition met in drop down list | oalrashdan | Excel Programming | 1 | 12-13-2013 02:21 AM |
drop down list from external data source Excel 2007? | Intruder | Excel | 1 | 08-03-2012 05:41 AM |