1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Populate a dropdown box in Word 2003 from a list in Excel 2003

Discussion in 'Business Applications' started by LauraJeffery, Jul 9, 2007.

Thread Status:
Not open for further replies.
  1. LauraJeffery

    LauraJeffery Thread Starter

    Jul 4, 2007
    I have a number of standard letters which require a dropdown box to be populated with a list of over 25 names.

    When thinking this through, I figured that it would be best (due to restrictions on the network so I'm unable to use an access database) to produce this list in Excel 2003. Along with corresponding lists of email and telephone numbers.

    Unfortunately I'm finding it very difficult to get the information onto the dropdown.

    So far the code I have is:

    Private Sub UserForm_Click()

    'Define Variables
    Dim xlNames As Excel.Workbook
    Dim rsNames As Recordset
    Dim cmbNames As ComboBox
    Dim i As Integer
    Dim SocServs As Worksheet

    'open the spreadsheet to access the information
    Set xlNames = Excel.Workbooks.Open("C:\Documents and Settings\00772367\My Documents\SocServs.xls")
    'set up and activate the appropriate worksheet
    Set SocServs = xlNames.Application.Sheets("SocServs")

    'retrieve the information from the named column in the spreadsheet
    Set rsNames = SocServs.Columns(1, 1)

    i = 0

    With rsNames
    ' This code populates the combo box with the values
    ' in the Names column of that there spreadsheet (I fervently hope).

    Do Until .EOF
    cmbNames.AddItem (i)
    cmbNames.Column(0, i) = .Fields("Names")
    i = i + 1

    End With
    End Sub

    There's a few errors in there, which I can't seem to surmount. The coding doesn't seem to recognise the fact that I've got a named range in the spreadsheet.

    I've been working on this now for about 1 week. I've trawled through many different websites of help, and have finally decided to ask, before the person I'm doing the work for gets too agitated, or alternatively I fling my pc out the window :)

    Many thanks, in advance, for any help you can offer.

  2. Rollin_Again


    Sep 4, 2003
    Is there any particular reason why you are needing to do this using VBA? You can populate a cell with your multiple choice options using Excel's built in Data Validation feature. Simply select the cell you want the options to appear in and click DATA --> VALIDATION and then select "ALLOW LIST" from the options. Then select the range of cells that contains your list of names.

  3. LauraJeffery

    LauraJeffery Thread Starter

    Jul 4, 2007
    The dropdown box needs to be in a form in Word. I'm merely trying to populate the dropdown box from Excel. And Word's dropdown boxes only allow 25 entries.

    Does that make sense?

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/593526

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice