XL97: Unable to Share Custom Lists

• Microsoft Excel 97 for Windows

IMPORTANT: This article contains information about editing the registry. Before you edit the registry, make sure you understand how to restore it if a problem occurs. For information about how to do this, view the "Restoring the Registry" Help topic in Regedit.exe or the "Restoring a Registry Key" Help topic in Regedt32.exe.

SYMPTOMS

In Microsoft Excel, there is no built-in or simple way to share a custom sort/fill list created in Microsoft Excel.

CAUSE

In Microsoft Excel 97, custom lists are more difficult to share because they are stored in the Windows Registry. In Microsoft Excel version 7.0, custom lists are stored in <username>.xlb; in version 5.0, they are stored in Excel5.xlb. Both of these files are stored in the \Windows folder.

RESOLUTION

To move a custom sort/fill list to another computer or user, you must export a Windows registry key. To locate and export the registry key that keeps the custom sort/fill list in Microsoft Excel, follow these steps.

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the "Changing Keys And Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it.

1. Quit Microsoft Excel.

2. On the Windows 95 taskbar, click the Start button, and then click Run.

3. In the Run box, type "regedit" (without the quotation marks), and then click OK.

4. Locate the following key:

   HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Excel \Microsoft Excel

5. Select the key listed in step 4.

6. Back up the registry entry you currently have for this registry key. On the Registry menu, click "Export Registry File." In the File Name box, enter a name for your backup, and then click Save.

7. In the right pane of the Registry Editor, click the first item in the Name column, and then press and hold the SHIFT key while you click the last item in the Name column.

   This selects all entries for the key specified in step 4.

8. Press the Control Key and then click "SmartList" in the Name column.

   This clears the selection of just this item in the list.

9. Click Delete on the Edit menu. Click Yes to confirm deleting the values. After it finishes deleting items, if you see the error message "Unable to delete all specified values," click OK.

   This removes all other keys except the desired item and the default key.

10. On the Registry menu, click "Export Registry File." In the File Name box, enter a name for this registry file. Note, it must be something different than the name you used in step 6.

    This is the registry file that you give to the computers that will share the custom sort/fill list.

11. Reset your registry setting for the key specified in step 4. On the Registry menu, click "Import Registry File" and in the "Import Registry File" dialog box double click the registry file you saved in step 6.

12. On the Registry menu, click Exit.

13. Copy the registry file you saved in step 10 to a floppy disk or to a shared network directory.

14. On the computer for which you want to copy over the custom sort/fill list, repeat steps 2 through 5.

15. On the Registry menu, click "Import Registry File," and locate the registry file you copied in step 13.

16. On the Registry menu, click Exit.

17. Start Microsoft Excel.

18. Click Options on the Tools menu, and then click the Custom Lists tab.

    The custom list you created is displayed in the Custom Lists shown in this Options tab.

   ARTICLE-ID: Q136393
   TITLE     : How to Modify the Windows Registry

   ARTICLE-ID: Q150360
   TITLE     : XL: How To Share Custom AutoFill List

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

REFERENCES

For more information about creating a custom sort or fill list, click the Index tab in Microsoft Excel 97 Help, type the following text

   custom fill series