Suggesties voorbeeldbestand
VBA customlist in cellen, comboboxen, listboxen en userforms

1 dag/maandnamen invoeren

2 Hoezo ?

3 VBA en customlists
3.1 autofill
3.2 vul een gebied
3.3 vul ActiveX-boxen
3.4 vul userform boxen
3.5 caveat

4 Eigen customlist
4.1 Methode I
4.2 Import
4.3 Split and Array()

5 Validatielijst
6 Sorteren

1 Invoer van weekdagnamen en maandnamen

Als je in cel A1 'ma' typt en je trekt met de vulgreep de cel door naar rechts (bijv. K1) of naar beneden (bijv. A10) dan verschijnen de dagen van de week in kleine letters.
Als je in cel A1 'maandag' typt en je vult door naar rechts of naar beneden dan krijg je de namen van de weekdagen voluit te zien.
Als je in cel A1 'jan' typt leidt uitvullen tot de verkorte maandnamen.
Als je in cel A1 'januari' typt krijg je na uitvulling de maandnamen voluit.

De letteropmaak (hoofdletters/kleine letters) van de uitvulling past zich aan aan wat je in de eerste cel heb ingevoerd:
- janleidt totfeb
- JantotFeb
- JANtot FEB

2 Hoezo

Dit verschijnsel hebben we te danken aan een 4-tal ingebouwde 'customlists' ('aangepaste lijst').
Je kunt ze zien via menubalk/extra/opties/tabblad aangepaste lijsten.
Dit zijn 1-dimensionele arrays.

3 VBA en customlists

Ook met VBA kun je customlists gebruiken.
Het voorbeeldbestand bevat de VBA-code voor deze bewerkingen:

3.1 cellen vullen zoals je het met de handmatige vulgreep doet : (geel gebied)

Cells(1, 1) ="ma"

Cells(1, 1).AutoFill Cells(1, 1).Resize(14)

3.2 cellen vullen door een aangepaste lijst rechtstreeks naar een gebied te schrijven (lichtgroen gebied)

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

3.3 comboboxen of listboxen in een werkblad vullen (donkerblauw gebied)

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

3.4 comboboxen of listboxen in een userform vullen

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

3.5 Caveat

De ingebouwde 'customlists' (weekdagnamen en maandnamen) hebben de onjuiste Nederlandse spelling, want beginnend met een hoofdletter.
Als je die rechtstreeks gebruikt, maak je dus een spelfout.
Dat kun je voorkomen door van de arrays een tekstreeks te maken, deze in kleine letters te converteren en van de tekstreeks weer een array te maken.
In de bijlage vind je daarvan illustraties in de comboboxen in Blad1 en de Listboxen in het Userform
Blad1.ListBox1.List = Split(LCase(Join(Application.GetCustomListContents(4), "|")), "|")

4 Maak je eigen customlist

Het kan handig zijn je eigen customlist te maken.
Stel je werkt met 12 teams : Team I, Team II, Team III, t/m Team XII.
Maak je eigen lijst met

4.1 Methode I

Excel <2007

- Menubalk/extra/opties/Aangepaste lijst
- Voer de namen van de teams in, gescheiden door komma's
- Klik op de knop Opslaan.

Excel >= 2007

- Lint / Bestand / Opties / Geavanceerd / (boven Lotus Compatibility) / Edit Customlists
- Voer de namen van de teams in, gescheiden door komma's
- Klik op de knop Opslaan.

4.2 Import

- Zet in cel A1 t/m A12 Team I ... t/m ... Team XII

Excel <2007

- Menubalk/extra/opties/Aangepaste lijst
- Zet in de range-selector A1:A12 en klik op de knop 'importeren'
- Maak A1:A12 leeg

Excel >= 2007

- Lint / Bestand / Opties / Geavanceerd / (boven Lotus Compatibility) / Edit Customlists
- Zet in de range-selector A1:A12 en klik op de knop 'importeren'
- Maak A1:A12 leeg

4.3 Split en 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","_")
Zet vervolgens in een willekeurige cel Team I en vul aanliggende cellen (vertikaal of horizontaal) met de vulgreep.
Alle Team-namen komen automatisch tevoorschijn.
Zo kun je bijvoorbeeld ook per team een lijst maken met de gegevens van teamleden.

5 Een customlist als validatielijst

Je kunt iedere customlist als validatielijst gebruiken.
Dat lukt niet met de UserInterface van Excel.
Daarvoor hebben we VBA nodig.
Een customlist is een 1-dimensionele array waarnaar niet verwezen kan worden in de validation.add methode.
Daarom moeten we de customlist eerst naar een tekststring converteren met komma's als itemscheidingsteken.

Bijv. een validatielijst van alle maandnamen in cel A4:
sheets(1).cells(4,1).validation.add xlvalidatelist,,,join(Application.GetCustomListContents(4),",")
Analoog kun je je eigen customlists als validatielijst gebruiken in alle openstaande werkboeken.
Customlists worden nl. opgeslagen op applicatienivo (=Excel), benoemde gebieden daarentegen alleen op werkboeknivo.

6 Sorteren met een customlist

Als je verschillende teams in een kolom hebt staan en je gaat die alfabetisch sorteren krijg je in ieder geval niet de volgorde van de Romeinse cijfers te zien.
Wil je dat de sortering plaatsvindt op opvolgend Romeins cijfer dan kan dat

- met de hand
Menubalk/Data/Sorteren/Opties/sorteervolgorde voor de eerste sleutel:
Kies hier de customlist van het team om volgens die volgorde (I t/m XII) te laten sorteren.

- met VBA
De te sorteren gegevens staan in D1:D20
De teamlijst is de laatst toegevoegde customlist.
Range("D1:D20").sort range("D1"), Ordercustom:=application.customListcount