XL: How To Share a Custom AutoFill List

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, custom AutoFill lists are stored in the Microsoft Excel toolbar file. You can exchange custom lists from one computer to another either by copying the Microsoft Excel toolbar file or by using a macro that creates the custom list from a spreadsheet.

The "More Information" section of this article contains a sample Microsoft Visual Basic for Applications macro (Sub procedure) that you can use to create a file and distribute it to users that need the custom lists.

• In Microsoft Excel 98, the Microsoft Excel toolbar file is called "Excel Toolbars (8)".
• In Microsoft Excel 97, the Microsoft Excel toolbar file is called <name>8.xlb, where <name> is the user's name (for example, Amanda8.xlb).
• In Microsoft Excel version 7.0, the Microsoft Excel toolbar file is called <name>.xlb, where <name> is the user's name (for example, Amanda.xlb).
• In Microsoft Excel for Windows, version 5.0, the Microsoft Excel toolbar file is called Excel5.xlb.
• In Microsoft Excel for the Macintosh, version 5.0, the Microsoft Excel toolbar is called "Excel Toolbars (5)".

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

1. Create a new Microsoft Excel workbook, and in column A of Sheet1, enter a list that contains 50 entries (that is, enter data in the range A1:A50).

2. Insert a module sheet, and type the following sample code:

         Sub Custom_List()
             Application.AddCustomList _
                 ListArray:=Sheets("Sheet1").Range("A1:A50")
         End Sub
   
   

3. Run the macro.

4. On the Tools menu, click Options or Preferences.

5. Select the Custom Lists tab. In the Custom Lists box, click the custom list you created (located at the bottom of the list). In the List Entries box you can view the custom list from Sheet1, range A1:A50.