How To Share a Custom AutoFill List (150360)

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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. To create and run the sample macro, follow these steps:

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

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

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

Run the macro.
On the Tools menu, click Options or Preferences.
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.