Suggestions example file
VBA customlist in cells, comboboxes, listboxes and userforms

1 Insert weekday/month names
2 How come ?
3 VBA and customlists
3.1 autofill
3.2 fill a range
3.3 fill ActiveX -boxes
3.4 fill userform boxes

4 Make your own customlist
4.1 Method I
4.2 Import
4.3 Split and Array()

5 Validationlist

6 Sorting

1 Insert weekdaynames and monthnames

If you insert 'mon' in cell A1 and you autofill to the right (e.g. K1) or down (e.g. A10) the abbreviated weekdaynames appear in the range.
If you insert 'monday' in cell A1 and you autofill either to the right or down the full weekdaynames will appear.
If you insert 'jan' in cell A1 autofill results in abbreviated monthnames.
If you insert 'january' in cell A1 autofill results in the full monthnames.

The formatting (lowercase/propercase/uppercase) of the autofill is dependent of what you enter in the first cell.
- janproducesfeb
- JanproducesFeb
- JANproducesFEB

2 How come ?

This phenomenon is the result of 4 builtin 'customlists'.
You can find them here: menubar/extra/options/tab customlists.
These 4 lists are builtin lists and can't be deleted/adapted.
They are 1-dimensional arrays.

3 VBA and customlists

How to use customlists in VBA (cfr. the example file)

3.1 fill cells with autofill: (yellow area in example file)

Cells(1, 1) ="mon"
Cells(1, 1).AutoFill Cells(1, 1).Resize(14)

3.2 fill a range writing directly to a range (lightgreen area in example file)

horizontally:
Cells(20,1).Resize(,UBound(Application.GetCustomListContents(2))) = Application.GetCustomListContents(2)
vertically:
Cells(1,10).Resize(UBound(Application.GetCustomListContents(1))) = Application.Transpose(Application.GetCustomListContents(1))

3.3 populate ActiveX comboboxes / listboxes (darkblue area in example file)

Blad1.OLEObjects("Combobox1").Object.List = Application.GetCustomListContents(2)

3.4 populate comboboxes / listboxes in a userform

Private Sub UserForm_Initialize()
For j = 1 To 4
Me("Combobox" & j).List = Split(LCase(Join(Application.GetCustomListContents(j), "|")), "|")
Controls("ListBox" & j).List = Application.GetCustomListContents(j)
Next
End Sub

4 Make your own customlist

Assuming you've got 12 teams : Team I, Team II, Team III, until Team XII.
Make your own customlist

4.1 Method I

Excel <2007

- Menubar/extra/options/Customlist
- Insert the teamnames, separated by commas
- Click the Button 'Save'

Excel >= 2007

- Ribbon / File / Options / Advanced / (above Lotus Compatibility) / Edit Customlists
- Insert the teamnames, separated by commas
- Click the Button 'Save'

4.2 Import

Excel <2007

- insert in range A1:A12 Team I ... Team XII
- Menubar/extra/options/Customlist
- Put into the rangeselector 'A1:A12' and click the button 'import'
- Clear range A1:A12

Excel >= 2007

- insert in range A1:A12 Team I ... Team XII
- Ribbon / File / Options / Advanced / (above Lotus Compatibility) / Edit Customlists
- Put into the rangeselector 'A1:A12' and click the button 'import'
- Clear range A1:A12

4.3 Split and Array()

Application.AddCustomList Split("Team I_Team II_Team III_Team IV_Team V_Team VI_Team VII_Team VIII_Team IX_Team X_Team XI_Team XII","_")
or
Application.AddCustomList Array("Team I","Team II","Team III","Team IV","Team V","Team VI","Team VII","Team VIII","Team IX","Team X","Team XI","Team XII")
Enter 'Team I' in a cell and autofill adjacent cells (horizontally or vertically).
All team names will appear automatically.
You could also devise lists with all members of certain teams.

5 Use customlists as validationlists

You can use any customlist as a validationlist.
We need VBA to do this because it's not possible to select a customlist as validationlist in the User Interface.
The customlist is a 1-dimensional array and can't be referred to in the arguments of the validation.add method.
We have to convert the customlist to a string, separated by commas before assigning it to the validation.

To enter a validationlist containing all monthnames in cell A4:
sheets(1).cells(4,1).validation.add xlvalidatelist,,,join(Application.GetCustomListContents(4),",")
In the same way you can also add your own specific customlists as validationlists in all workbooks you are working with.
The main advantage of customlists is, that they are stored on the application level: Excel, while named ranges are being stored in a workbook.

6 Sorting using customlists

You can use the buitin customlists to sort a list of items.

Sort by abbreviated weekday name: sun, mon, tue, etc. using customlist 2
Cells(1).CurrentRegion.Sort Cells(1), , , , , , , , 2
Sort by weekday name: sunday, monday, tuesday, etc. using customlist 3
Cells(1).CurrentRegion.Sort Cells(1), , , , , , , , 3
Sort by abbreviated monthname: jan, feb, mar, apr, etc. using customlist 4
Cells(1, 5).CurrentRegion.Sort Cells(1, 5), , , , , , , , 4
Sort by monthname: january, february, march, april etc. using customlist 5
Cells(1, 7).CurrentRegion.Sort Cells(1, 7), , , , , , , , 5
You can also use your own custommade customlist. If you sort these teams alphabetically you will not get the correct order of the Roman numbers.
If you want the appropriate order you can use the customlist as the sorting criterion.

- manually
Menubar/Data/Sort/Options/sortingorder for the first sorting key:
Choose the 'team' customlist to get the sorting (I t/m XII).

- in VBA
Assuming the data that have to be sorted reside in range D1:D20
Assuming the 'team' customlist is the last added customlist.
In the sorting argument 'Ordercustom' you have to indicate the number of the customlist you want to use as criterion.
Range("D1:D20").sort range("D1"), Ordercustom:=application.customListcount