XL: How To Share a Custom AutoFill List

ID: Q150360

The information in this article applies to:

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)".

In all cases, the toolbar file is stored in the Windows directory (for example, C:\Windows) or in the System Folder:Preferences folder (on the Macintosh).

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 professionals 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/

To create and run the sample macro, follow these steps:

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.

To share this custom list with other users, name the macro Sub Auto_Open().

This will cause the macro to run as soon as the file is opened and will distribute the workbook. If you name the macro something other than Sub Auto_Open, you will need to distribute the workbook and advise users to run

the macro when they get the file.

Additional query words: 5.00 5.00a 5.00c 7.00 7.00a 8.00 XL97 XL98 XL7 XL5

Keywords          : kbprg kbualink97 kbdta kbdtacode PgmHowto KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: May 18, 1999