Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form Fields--DropDown List for 500 Options (Word 2000)

    We are creating a new Word form (Journal Voucher) where there is a field for Account No. & Name. My boss wanted a dropdown list for this field; however, I just learned that there are nearly 500 accounts to choose from! These 500 account names are currently listed in an Excel file.

    Could a macro be written where, upon entering this field, the user could be given a pick list to choose from the 500 accounts, perhaps taking the user directly to the Excel file so that he/she could pick from the Excel list and have the selection copied into the field in the Word form? The form will be set up for a possible 20 rows where this particular field is the first of five fields per row. Any suggestions would be greatly appreciated...Mary

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Form Fields--DropDown List for 500 Options (Word 2000)

    Mary,

    First thought is, if the 500 names are kept in an Excel file, perhaps the Journal Voucher should be created in Excel, rather than Word? (Although even in Excel, you'd still have issues with working out the best way to present 500 items for the user to choose from, in a manageable way.)

    If you are determined to do this in Word, one solution might be to have a custom "Enter Row Data" userform that could appear every time the user needs to add data to a new row on the form. The user would make selections/enter data into the userform, and then when they click an OK button, the data from the userform, gets entered in to the next empty row in the form.

    As far as displaying the 500 items in a manageable way, on the userform you could combine option buttons with a list box - say four option buttons for "A-F", "G-M", "N-J", and "T-Z" - when the user clicks on any of the option buttons, the list box would repopulate with the list items whose first letters fall within the selected letter range.

    The code would also have to automate an instance of Excel, open the file and get hold of the account names, and put them into an array that can be filtered.

    The above isn't trivial, but it should be possible to do. I've got some similar code that could perhaps be adapted to produce a sample, but it's not available where I am right now. If time permits, I can try to post a sample in the next day or two.

    Hope this helps,
    Gary

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Fields--DropDown List for 500 Options (Word 2000)

    Many thanks, Gary; I appreciate your advice. Because I am just a novice at Visual Basic programming, I've decided to just convert the Excel list of accounts into a Word document (2-column list) and place it as a 2nd page, unprotected section to the form. The user can select the appropriate account (account names are up to 54 characters long) and then copy the selection into the appropriate field. When the user finishes completing the form and sends it to print, I've included code to delete the list (or the unprotected section) and just print the completed form....At least it's better than what they had been doing--hand-writing all their entries into hard copy journal voucher forms! Once again, thanks for your help...Mary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •