Custom Lists

[Guest]

I have a workbook that I have developed a set of custom list criteria.
However, this list was created on the home computer (faster). I run the
program mainly on the laptop.
My question is: when the program gets moved over to the laptop is there an
easy way (macro etc) to transfer the custom list over to the laptop at the
same time?

Thanks in advance

 

[Debra Dalgleish]

Custom lists are stored in the Windows registry. There's information in
the following MSKB article:

XL2000: How To Share a Custom AutoFill List
http://support.microsoft.com/?kbid=213796

 

[Guest]

Followed the link, however, the macro in the example doesn't work on Excel XP
, which is what I'm running. Any other suggestions?

 

[Debra Dalgleish]

The technique should work the same in Excel 2002 as is described for
Excel 2000.

You can use the following two macros. Run the first one (GetCustomLists)
to extract the custom lists from your home computer. Copy that workbook
to your laptop, and run the second macro (Custom_List), to add the
custom lists to that machine.

'========================
Sub GetCustomLists()
'extract custom lists from registry
Dim listArray As Variant
Dim iItems As Integer
Dim iLists As Integer
Dim ws As Worksheet
For iLists = 5 To Application.CustomListCount
listArray = Application.GetCustomListContents(iLists)
Worksheets.Add
Set ws = ActiveSheet
For iItems = LBound(listArray, 1) To UBound(listArray, 1)
ws.Cells(iItems, 1).Value = listArray(iItems)
Next iItems
Next iLists
End Sub
'=======================================
Sub Custom_List()
'add custom lists from active workbook to registry
Dim ws As Worksheet
Dim listArray As Variant
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Application.AddCustomList _
listArray:=ws.Range("A1").CurrentRegion
Next ws
End Sub

'============================