Microsoft Office Forums Having a Drop-down list in Word referring to an Excel list

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-07-2016, 11:00 PM
celias celias is offline Having a Drop-down list in Word referring to an Excel list Windows 10 Having a Drop-down list in Word referring to an Excel list Office 2016
Novice
Having a Drop-down list in Word referring to an Excel list
 
Join Date: Jul 2016
Posts: 5
celias is on a distinguished road
Question 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.
Reply With Quote
  #2  
Old 07-07-2016, 11:20 PM
gmayor's Avatar
gmayor gmayor is offline Having a Drop-down list in Word referring to an Excel list Windows 10 Having a Drop-down list in Word referring to an Excel list Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,764
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 07-11-2016, 10:53 PM
celias celias is offline Having a Drop-down list in Word referring to an Excel list Windows 10 Having a Drop-down list in Word referring to an Excel list Office 2016
Novice
Having a Drop-down list in Word referring to an Excel list
 
Join Date: Jul 2016
Posts: 5
celias is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 07-11-2016, 11:40 PM
gmayor's Avatar
gmayor gmayor is offline Having a Drop-down list in Word referring to an Excel list Windows 10 Having a Drop-down list in Word referring to an Excel list Office 2016
Expert
 
Join Date: Aug 2014
Posts: 2,764
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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 third box contains a the xlFillList function. It is shown as a Public function so it can go either in a new module in the document template, or it can go in the userform code module and the userform can access it from either place.

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
See http://www.gmayor.com/installing_macro.htm
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
Reply

Tags
drop down lists, excel list, word template

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Having a Drop-down list in Word referring to an Excel list How to import list from Excel into drop-down list into word ahw Word VBA 40 07-25-2018 02:58 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
Having a Drop-down list in Word referring to an Excel list (Excel 2007) Need help with colour change when condition met in drop down list oalrashdan Excel Programming 1 12-13-2013 02:21 AM
Having a Drop-down list in Word referring to an Excel list drop down list from external data source Excel 2007? Intruder Excel 1 08-03-2012 05:41 AM


All times are GMT -7. The time now is 06:53 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft