Using VBA to control Custom Lists

In Excel it is possible to create Custom Lists which are available for use in the spreadsheet or VBA application.  The default lists provided by Microsoft are the days of the week Monday – Sunday (or the shorter version of Mon-Sun) and the months of the year January – December (or the shorter version Jan-Dec).

The most common uses for Custom Lists are to speed up data entry on a spreadsheet.  They exist on the application level and are therefore only present on the computer on which they were created.  However, by using the VBA code snippets below it is possible to automatically add, find, delete and apply these Custom Lists.

Adding a new Custom List

'Add a new Custom List
Application.AddCustomList ListArray:=Array("Element1", "Element2", "Element3")

'Add a new Custom List from range of cells
Application.AddCustomList ListArray:=Range("Sheet1!A1:A3")

Count the number of Custom Lists

'Count the number of Custom Lists
Dim noOfLists As Integer
noOfLists = Application.CustomListCount

Find if a Custom Lists already exists

'Find the listNum of a Custom List based on all items
Dim listNumFound As Integer
listNumFound = Application.GetCustomListNum(Array("Element1", "Element2", "Element3"))


'Find a Custom List which contains a specific element 
Dim i As Integer
Dim arrayItem As Variant
Dim customListContents() As Variant
Dim listNumFound As Integer

For i = 1 To Application.CustomListCount

    'Set the CustomList array to a variable
    customListContents = Application.GetCustomListContents(i)

    'Loop through each element in the CustomList
    For Each arrayItem In customListContents

        'Test if the element has a specific value
        If arrayItem = "Element1" Then listNumFound=i

    Next arrayItem

Next i

Delete a Custom List

'Delete a Custom List
Application.DeleteCustomList listNum:=5

Note: listNum 1-4 are reserved for defaults set by Microsoft

Edit a Custom List

There is no way to edit an existing Custom List.  The best option is to delete the list, then create a new list.

Apply a Custom List to a range of cells

'Apply an existing Custom List to a range of cells
Range("D5").FormulaR1C1 = "Element1"
Selection.AutoFill Destination:=Range("D5:D7")

---

What next?

Discover how you can automate your work with our Excel courses and tools.

The Excel Academy