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.
Advertisement
  1. LauraJeffery

    LauraJeffery Thread Starter

    Joined:
    Jul 4, 2007
    Messages:
    2
    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")
    SocServs.Activate

    '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")
    .Next
    i = i + 1
    Loop

    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.


    Laura
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    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.

    Regards,
    Rollin
     
  3. LauraJeffery

    LauraJeffery Thread Starter

    Joined:
    Jul 4, 2007
    Messages:
    2
    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?

    Laura
     
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!

Loading...
Similar Threads - Populate dropdown Word
  1. ftgml
    Replies:
    1
    Views:
    125
Thread Status:
Not open for further replies.

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