VBA voor smarties: ComboBox

ComboBox

1. Wat is een ComboBox

2. Waarvoor een ComboBox

3. Herkomst ComboBox

4. Maak een ComboBox
4.1 In de ontwerpmodus
4.2 Tijdens macro-uitvoering

5. Karakter van ComboBox
5.1 Array
5.2 Excel werkblad

6. Vul een ComboBox
6.1 Additem
6.1.1 leeg item
6.1.2 item met waarde
6.1.3 item > 10 kolommen
6.2 1-dimensionele array
6.2.1 .List
6.2.2 .Column
6.2.3 Array
6.2.4 Split
6.2.5 Variant Array
6.3 2-dimensionele array
6.3.1 Variant Array
6.3.2 Userformcontrol
6.3.3 Range van Excel-blad
6.3.4 Listobject
6.3.5 Dictionary
6.3.6 Database
6.3.6.1 Access bestand
6.3.6.2 Excel-bestand
6.3.6.3 Tekstbestand
6.3.6.4 Web: Access bestand
6.3.6.5 Web: Excel bestand
6.3.6.6 Web: Tekst bestand

7 Waarde in ComboBox ?
7.1 .Value
7.2 Excel-methode Match

8. Omvang ComboBox
8.1 Aantal items/rijen
8.1.1 .ListCount
8.1.2 .List
8.1.3 .Column
8.2 Aantal kolommen
8.2.2 .List
8.2.3 .Column

9. Lezen/ophalen
9.1 1 element
9.2 filteren
9.2.1 ComboBox met 1 rij/kolom
9.2.2 rij/kolom 2-dim. ComboBox
9.3 filteren 2-dim. ComboBox
9.3.1 1 kolom
9.3.2 meer kolommen
9.3.3 2 of meer items
9.3.4 bepaalde kolommen/items

10 wijzig element
10.1 onafhankelijk van .Listindex
10.1.1 .List
10.1.2 .Column
10.2 geselecteerd element

11 Transponeer ComboBox

12 ComboBoxgegevens schrijven
12.1 Array
12.2 getransponeerde Array
12.3 Excel werkblad
12.4 Dictionary

13. sorteren
13.1 1 gegevenskolom
13.1.1 Excel werkblad
13.1.2 Arraylist
13.1.3 Sortedlist
13.1.4 ADODB recordset
13.2 verschillende kolommen
13.2.1 Excel werkblad
13.2.2 ADODB-recordset

14. verwijderen
14.1 1 element
14.2 diverse elementen
14.3 alle elementen

1. Wat is een ComboBox ?

Een ComboBox in VBA is een verzamelobject: je kunt er getallen, teksten en datums in opbergen.
VBA heeft verschillende andere mogelijkheden om gegevens op te slaan:
- een dictionary
- een collection
- een array variabele
- een ActiveX ComboBox
- een ActiveX ListBox
- een ListBox in een Userform
- een sortedlist

De keuze voor een van deze methoden is afhankelijk van het te bereiken doel.
In deze pagina wordt geen poging gedaan al deze methoden met elkaar te vergelijken.
We beperken ons tot een bespreking van de mogelijkheden van de ComboBox in een Userform.
De ComboBox heeft één onderscheidend kenmerk.
In de ComboBox kun je in 1 keer één grote Array met gegevens zetten.
De rijen van die Array kun je vervolgens als afzonderlijke items/records bewerken.
Ook de Listbox in een Userform heeft dit onderscheidende kenmerk.

2. Waarvoor kun je een ComboBox gebruiken ?

Je kunt de ComboBox gebruiken om gegevens bij elkaar te zetten en snel te bewerken.
Via de ComboBox zijn ze nl. in het werkgeheugen geladen.
In plaats van gegevens te bewerken in een Excel-werkblad, een Word Document, een Powerpointpresentatie, doe je dat in het werkgeheugen.
Daarvoor hoeft bijv. geen scherm ververst te worden, hoeven geen berekeningen uitgevoerd te worden.

Een Userform kan een ComboBox bevatten.
Een Userform is meestal zichtbaar voor de gebruiker.
Je kunt een Userform met een ComboBox ook laden zonder aan de gebruiker te tonen.
De ComboBox in het Userform kan worden gevuld met gegevens uit een database (uit het werkboek, een externe gegevensbron).
Die gegevens zijn dan voor de gebruiker in het hele bestand beschikbaar.
In tegenstelling to Arrays, Dictionaries of Collections is daarvoor geen declaratie van een Public variabele in een gewone macromodule vereist.

Voorbeeld
Bij het openen van het werkboek wordt het userform geladen, maar niet getoond:

Private Sub Workbook_open()

Load Userform1

End sub

Als het Userform wordt geladen wordt ComboBox1 gevuld met de gegevens van Tabel 'Q_test' in bestand 'G:\fiets.accdb'.

Private Sub Userform_Initialize()

With CreateObject("ADODB.recordset")

.Open "SELECT * FROM Q_test", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\fiets.accdb"
ComboBox1.Column = .GetRows

End With

End Sub

Nu kan in het werkblad de volgende code komen:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$E$24" Then

UserForm1.ComboBox1.Value = Target.Value
Target.Offset(, 1) = UserForm1.ComboBox1.Column(2)

End if

End Sub

Als de gebruiker een gegeven invoert in cel E24 wordt de bijkomende waarde in de derde kolom uit de database in cel F24 gezet.
Het onzichtbare Userform met de ComboBox fungeert in het werkgeheugen als vervanger voor een Querytable en vervanger voor een VLookupformule.

3. Waar komt de ComboBox vandaan ?

Er zijn in Office verschillende ComboBoxen:
- ComboBox als Active-X control
- ComboBox als CommandBarControl: onderdeel van een werkbalk
- ComboBox als VBA-object op basis van de GUID
- ComboBox als element/control in een Userform

Op deze pagina komt alleen de ComboBox als Userformcontrol aan bod.
De ComboBox als CommandBarcontrol niet, omdat die geen 2-dimensionele arrays kan bevatten.
Ook de ComboBox op basis van de GUID kan geen 2-dimensionele Array bevatten; daarom bespreek ik hem hier niet.
De ComboBox als Active-X control is in grote lijnen vergelijkbaar met de ComboBox in een Userform.
Een uitgebreide behandeling van de Userform ComboBox biedt dan voldoende inzicht om die toe te passen op de Active-X control.

De UserformComboBox maakt geen deel uit van de standaard VBA-bibliotheek.
De ComboBox is onderdeel van de bibliotheek Microsoft Forms 2.0 Object Library.
Die bevindt zich in het bestand C:\WINDOWS\system32\FM20.DLL of een vergelijkbare directory
Deze bibliotheek wordt geladen in Excel en Word zo gauw je een Userform aanmaakt.
Daardoor is deze ComboBox altijd een onderdeel ('control') van een Userform.

4. Hoe maak je een ComboBox in een UserForm ?

4.1 In de ontwerpmodus

In de ontwerpmodus kun je een ComboBox aan een Userform toevoegen.
Het is verstandig meteen zoveel mogelijk ComboBox eigenschappen in de ontwerpmodus vast te leggen.
Dan kun je meteen de positie van ComboBox in het Userform en de opmaak (bijv. grootte, lettertype en -grootte, achtergrond- en voorgrondkleur) bepalen.
De enige eigenschap die je niet in de ontwerpmodus kunt instellen is welke elementen de ComboBox moet gaan bevatten.
Daarvoor zijn 2 eigenschappen, .List en .Column, te gebruiken en/of de methode .additem.
Welke gegevens getoond worden kan ook in de ontwerpmodus vastgelegd worden met .Columncount en .Columnwidth.
Als de omvang van de gegevens in de ComboBox variabel is kunnen deze beide eigenschappen ook vastgelegd worden nadat de ComboBox gevuld is.

4.2 Tijdens het laden van het userform

Het verdient de voorkeur de ComboBox in de ontwerpmodus aan het Userform toe te voegen.
Als vooraf nog niet bekend is hoeveel ComboBoxen aan de gebruiker getoond moeten worden, kun je het maximale aantal ComboBoxen ontwerpen en onzichtbaar maken.
Pas als de gegevens in het Userform of de akties van de gebruiker daarvoor aanleiding geven kun je via de code in het Userform de onzichtbare ComboBoxen zichtbaar maken.
De - niet aan te bevelen - code om een ComboBox aan het Userform 'Invoer' toe te voegen bij bijv. het openen van het Userform is:

Private Sub Userform_Initialize()

ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("Forms.ComboBox.1").Name = "ComboBox_1"

End Sub

5. Het karakter van een ComboBox

5.1 Array

De ComboBox heeft alle kenmerken van een 2-dimensionele Array.
Een aantal eigenschappen van een Array kun je ook gebruiken voor een ComboBox: LBound en Ubound.
In bepaalde gevallen kun je ook methodes als Filter en Join gebruiken.
De ondergrens van het aantal rijen in een ComboBox is 0; de ondergrens van het aantal kolommen is 0.
De eigenschap .List benadert de omvang van de ComboBox primair vanuit de rijen, secundair de kolommen.
De eigenschap .Column benadert de omvang primair vanuit de kolommen, secundair de rijen.

Het aantal rijen in een ComboBox kun je vanuit beide eigenschappen (.List en .Column) bepalen:

y = Ubound(ComboBox1.List) + 1
y = Ubound(ComboBox1.Column,2) + 1

Het aantal kolommen in een ComboBox kun je ook vanuit beide eigenschappen (.List en .Column) bepalen:

y = Ubound(ComboBox1.List,2) +1
y = Ubound(ComboBox1.Column) + 1

NB. De eigenschap ComboBox1.Columncount geeft niet het aantal kolommen van de inhoud van de ComboBox weer maar het aantal te tonen kolommen.

5.2 Excel werkblad

Ook een Excel werkblad heeft alle kenmerken van een 2-dimensionele Array.
Een ComboBox en een Excel werkblad zijn min of meer als uitwisselbaar te beschouwen.
Veel Excelformules zijn toe te passen op Arrays.
Vanwege het Array-karakter van de ComboBox zijn deze Excelformules ook toe te passen op de ComboBox.

De som van alle waarden in de ComboBox

ComboBox1.List=Array(2,4,8,12,30)
Msgbox Application.sum(ComboBox1.List)

filter alle waarden die de tekst 'bb' bevatten

ComboBox1.List = Array("aa1", "aa2", "bb3", "bb4", "bb5", "cc6", "cc7")
ComboBox1.List = Filter(Application.Transpose(ComboBox1.List), "bb")

6. Hoe vul je een ComboBox

De inhoud van een ComboBox kan alleen tekst of getallen bevatten.

Je kunt aan een ComboBox afzonderlijke items toevoegen of een Array van items in de ComboBox zetten.
Ook als je in een ComboBox een Array zet maakt de ComboBox er afzonderlijke 'rijen'/'records'/'elementen' van.
Ieder element in een ComboBox is altijd een 2-dimensionele array van 1 rij en x kolommen.

6.1 afzonderlijke items toevoegen

6.1.1 methode .Additem: leeg item

De methode .Additem plaatst een item achter het laatste element van de ComboBox.
Later komt aan bod hoe je kunt bepalen waar een bepaald item in de ComboBox ingevoegd moet worden.
Je kunt een leeg item in een ComboBox zetten.

With ComboBox1

.Add

End With

Dan blijkt dat je feitelijk geen element in de ComboBox hebt gezet, maar een lege array van 1 rij en 10 kolommen

With ComboBox1

.AddItem

c00 = c00 & "_aantal rijen : " & Ubound(ComboBox1.List) + 1 & "||Ubound(ComboBox1.List) + 1"
c00 = c00 & "_aantal kolommen: " & Ubound(ComboBox1.List, 2) + 1 & "|Ubound(ComboBox1.List,2) + 1"
c00 = c00 & "_aantal kolommen: " & Ubound(ComboBox1.Column) + 1 & "|Ubound(ComboBox1.Column) + 1"
c00 = c00 & "_aantal rijen: " & Ubound(ComboBox1.Column, 2) + 1 & "||Ubound(ComboBox1.Column, 2) + 1"
c00 = c00 & "_aantal elementen: " & ComboBox1.ListCount & "||ComboBox1.ListCount"
c00 = c00 & "_aantal zichtbare kolommen: " & ComboBox1.ColumnCount & "|ComboBox1.ColumnCount"

' 1
' 10
' 10
' 1
' 1
' 1

Msgbox Replace(Replace(c00,"_",vblf),"|",vbtab)

End With

6.1.2 methode .Additem: item met waarde

Je kunt tegelijkertijd een 'item' (=array met 1 rij en 10 kolommen) toevoegen en een waarde in de eerste kolom van de array.
Die waarde kan een tekenreeks, een getal, of een datum zijn.

tekst

With ComboBox1

.Additem "tekst"

End With

getal

With ComboBox1

.Additem 23786

End With

datum

With ComboBox1

.Additem date

End With

6.1.3 methode .Additem: item met meer dan 10 kolommen

Als je gegevens met meer dan 10 kolommen/velden wil inlezen dien je ervoor te zorgen dat er als eerste een array met een grotere omvang aan kolommen in de ComboBox gezet wordt.
Dat kan met de eigenschap .List of .Column.

With ComboBox1

.Column = Split(Space(20))
.Additem

End With

Na deze code heeft de ComboBox de volgende eigenschappenwaarden:

aantal rijen: Ubound(ComboBox1.List): 1
aantal kolommen: Ubound(ComboBox1.List,2): 20
aantal rijen: ComboBox1.Listcount: 2

6.2 een 1-dimensionele array invoegen

Een ComboBox accepteert dat een groep van items tegelijkertijd aan de ComboBox wordt toegevoegd.
Voorwaarde is wel dat die items in een 'Array' staan.
De omvang van de ComboBox past zich vanzelf aan aan de toegewezen Array.
Voor het vullen van een ComboBox met een Array kun je de eigenschap .List of .Column gebruiken.
De manier waarop de ComboBox wordt gevuld is afhankelijk van de methode die je kiest.

6.2.1 eigenschap .List

De methode .List beschouwt een 1-dimensionele Array als een Array met evenveel 'records/rijen' als de Array elementen heeft.
De ComboBox bevat na het vullen met .List evenveel elementen/rijen als de toegewezen Array in 1 kolom: column 0.

With ComboBox1

.List = Array(1, 2, 3, 4, 5)

End With

Na deze code heeft de ComboBox de volgende eigenschappenwaarden:

aantal rijen: Ubound(ComboBox1.List): 4
aantal kolommen: Ubound(ComboBox1.List,2): 0
aantal rijen: ComboBox1.Listcount: 5

6.2.2 eigenschap .Column

De methode .Column transponeert de 1-dimensionele Array en beschouwt een 1-dimensionele Array als een Array met evenveel 'velden/kolommen' als de Array elementen heeft.
De ComboBox bevat na het vullen met .Column evenveel velden/kolommen als de toegewezen Array in 1 rij: row 0.

With ComboBox1

.Column = Array(1, 2, 3, 4, 5)

End With

Na deze code heeft de ComboBox de volgende eigenschappenwaarden:

aantal rijen: Ubound(ComboBox1.List): 0
aantal kolommen: Ubound(ComboBox1.List,2): 4
aantal rijen: ComboBox1.Listcount: 1

6.2.3 methode Array

Een Array kan teksten, getallen, datums of een combinatie van deze gegevens bevatten.

getallen

With ComboBox1

.List = Array(1, 2, 3, 4, 5)

End With

tekstreeksen

With ComboBox1

.List = Array("aa", "bb", "cc", "dd", "ee")

End With

datums

With ComboBox1

.List = Array(Date, Date + 1, Date + 2, Date + 3, Date + 4) ' US notatie

End With

Wanneer je op deze manier datums in een ComboBox zet, krijgen ze automatisch de opmaak van de Verenigde Staten.
Als je dat niet wil kun je de NL-notatie krijgen met de funktie 'Format'.
Die wijzigt de datum in een tekstreeks.

With ComboBox1

.List = Array(Format(Date, "dd-mm-yyyy"), Format(Date + 1, "dd-mm-yyyy"), Format(Date + 2, "dd-mm-yyyy"), Format(Date + 3, "dd-mm-yyyy"), Format(Date + 4, "dd-mm-yyyy"))

End With

With ComboBox1

.List = Array(Format(Date), Format(Date + 1), Format(Date + 2), Format(Date + 3), Format(Date + 4), Format(Date + 5))

End With

getallen, tekstreeksen, datums

With ComboBox1

.List = Array(1, "twee", Date-3, 4, "vijf")

End With

6.2.4 methode Split

Wanneer niet de methode 'Array' gebruikt wordt, maar 'Split' om een 1-dimensionele array te maken ontstaat altijd een array die alleen maar tekstreeksen bevat.
Dat kan de US-weergave van datums voorkómen.

getallen als tekst in ComboBox

With ComboBox1

.List = Split("1 2 3 4 5")

End With

tekst in ComboBox

With ComboBox1

.List = Split("aaa bbb ccc ddd eee")

End With

datums in NL-notatie als tekst in ComboBox

With ComboBox1

.List = Split(Format(Date, "dd-mm-yyyy ") & Format(Date + 1, "dd-mm-yyyy ") & Format(Date + 2, "dd-mm-yyyy")

End With

With ComboBox1

.List = Split(Date & " " & Date + 1 & " " & Date + 2 & " " & Date + 3 & " " & Date + 4)

End With

6.2.5 gedeclareerde Array

Je kunt een Arrayvariabele declareren en die vervolgens toewijzen aan de ComboBox.

Dim sn(12)
ComboBox1.List=sn

ReDim sn(12)
ComboBox1.List=sn

6.3 2-dimensionele array toewijzen

6.3.1 gedeclareerde Array

Wanneer een 2-dimensionele Array is gedeclareerd met Dim of Redim kan die aan de ComboBox worden toegewezen.

Dim sn(12,20)
ComboBox1.List=sn

ReDim sn(12,20)
ComboBox1.List = sn

De waarden kunnen in de Array worden gezet voordat die aan de ComboBox wordt toegewezen

Dim sn(12,20)

For j=0 To Ubound(sn)

For jj=0 To Ubound(sn,2)

sn(j,jj)="item " & j*jj

Next

ComboBox1.List=sn

Of de waarden kunnen in de 'records/velden' van de ComboBox worden gezet nadat de array aan de ComboBox is toegewezen.

ReDim sn(12,20)
ComboBox1.List = sn

For j=0 To Ubound(sn)

For jj=0 To Ubound(sn,2)

ComboBox1.List(j,jj)="item " & j*jj

6.3.2 array uit ander userformcontrol

Iedere gevulde ComboBox of Listbox in een Userform bevat een 2-dimensionele array.
Deze kan 1 op 1 toegewezen worden aan een andere ComboBox of ListBox.

Dim sn(12,20)

ComboBox1.List=sn
ComboBox2.List=ComboBox1.List

6.3.3 Range van een Excel-werkblad

Als de waarden van een gebied (Range) in een Excelblad aan een variabele worden toegewezen wordt die variabele automatisch een 2-dimensionele array variabele van het type Variant.
Deze array-variabele kun je dan in 1 keer toewijzen aan een ComboBox.

sn = sheets(1).range("A1:K10")
ComboBox1.List = sn

NB. De variabele sn heeft als ondergrens (LBound) 1, nadat de variabele is toegewezen aan de ComboBox heeft de ComboBox als ondergrens (LBound) 0.
Waarde sn(1,1) wordt dan ComboBox.List(0,0).

De waarden van een gebied kunnen ook rechtstreeks aan een ComboBox worden toegewezen.
Je zult VBA wel moeten helpen; de ComboBox converteert het Object 'Range' nl. niet automatisch naar de default-eigenschap van de Range: de waarde (.Value).
Je zult de eigenschap .Value moet toevoegen om VBA duidelijk te maken dat het je niet om het Range-Object te doen is, maar om de waarden die het Object bevat.

ComboBox1.List = sheets(1).range("A1:K10").Value

6.3.4 Listobject in een Excel-werkblad

Een Excel werkblad kan een tabel bevatten (in VBA Listobject), waarvan je de waarden van de verschillende onderdelen ook in een ComboBox kunt zetten.
Dat kan via de stap van een Array-variabele of rechtstreeks met de .Value eigenschap.

De gehele tabel

sn = Sheets(1).Listobjects(1).Range.Value
ComboBox1.List = sn

ComboBox1.List = Sheets(1).ListObjects(1).Range.Value

De gegevens zonder kopteksten

sn = Sheets(1).Listobjects(1).DataBodyRange
ComboBox1.List = sn

ComboBox1.List = Sheets(1).ListObjects(1).DataBodyRange.Value

De kopteksten

sn = Sheet1.ListObjects(1).HeaderRowRange
ComboBox1.List = sn

ComboBox1.List = Sheets(1).ListObjects(1).HeaderRowRange.Value

6.3.5 Dictionary

In een Dictionary kun je gegevens aan een unieke sleutel koppelen.
De koppelingen tussen sleutel en gegeven kun je als 2-dimensionele array aan een ComboBox toewijzen.

With CreateObject("scripting.dictionary")

For j = 1 To 10

.Item("A_" & j) = "BB_" & j

Next
ComboBox1.List = Application.Transpose(Array(.keys, .Items))

End With

Een dictionary kan items bevatten met 1-dimensionele arrays.
Als die 1-dimensionele arrays eenzelfde omvang hebben kun je de items samenvoegen tot een 2-dimensionele array met de Excel-methode 'Index' en toewijzen aan een ComboBox.

sn = sheets(1).Range("A1:K10")

With CreateObject("scripting.dictionary")

For j = 1 To Ubound(sn)

.Item(.Count) = Application.Index(sn, j)

Next

ComboBox1.List = Application.Index(.Items, 0, 0)

End With

6.3.6 Database-gegevens

6.3.6.1 Access bestand

Je kunt gegevens uit een Access-bestand met de ADODB-bibliotheek rechtstreeks aan de ComboBox toewijzen.
In het voorbeeld komen de gegevens in de velden 'adres' en 'plaats' uit de tabel 'Q_test' in bestand "G:\fiets.mdb".
De methode .GetRows zet ze in de ComboBox.
In tegenstelling tot wat je op basis van de naam zou verwachten bestaat de 1e dimensie van .GetRows niet uit rijen, maar uit kolommen.
Wil je de gegevens per 'record' in de ComboBox tonen, dan zul je de gegevens uit de database moeten transponeren.
Dat doet de methode ComboBox1.Column.

Access mdb bestand

c00 = "G:\fiets.mdb"
c01 = "Q_test"
c02 = "`adres`,`plaats`"

' bestand
' tabel
' velden

With CreateObject("ADODB.recordset")

.Open "SELECT " & c02 & " FROM " & c01, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & c00
ComboBox1.Column = .GetRows

End With

Wil je alle velden uit een tabel gebruiken:

c00 = "G:\fiets.mdb"
c01 = "Q_test"

' bestand
' tabel

With CreateObject("ADODB.recordset")

.Open "SELECT * FROM " & c01, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & c00
ComboBox1.Column = .GetRows

End With

Access accdb bestand, vanaf Office 2007

c00 = "G:\fiets.mdb"
c01 = "Q_test"

' bestand
' tabel

With CreateObject("ADODB.recordset")

.Open "SELECT * FROM " & c01, "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & c00
ComboBox1.Column = .GetRows

End With

6.3.6.2 Excel-bestand

Alle velden uit werkblad 'Q_test' van bestand 'G:\Q_test.xls' of 'G:\Q_test.xlsx'

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM `Q_test$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Q_test.xlsx;Extended Properties=""Excel 12.0 Xml"""
ComboBox1.Column = .getrows

End With

Alternatieve methode
Alle velden uit werkblad 'Q_test' van bestand 'G:\Q_test.xls' of 'G:\Q_test.xlsx'

With GetObject("G:\Q_test.xls")

ComboBox1.List = .Sheets("Q_test").UsedRange.Value
.Close 0

End With

6.3.6.3 Tekstbestand

Een tekstbestand met de reguliere lijst- en recordscheidingstekens kun je ook als database inlezen in de ComboBox.
Dit geldt in ieder geval voor .txt en .csv-bestanden.

c00 = "G:\"
c01 = "uitdraai6a.txt"

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM " & c01, "Driver={Microsoft Text Driver (*.txt; *.csv)};" & "Dbq=" & c00
ComboBox1.Column = .getrows

End With

6.3.6.4 Accessbestand uit website

Een Access-bestand kun je niet rechtstreeks vanaf een website inlezen in de ComboBox.
Net zoals bij een Querytable/Listobject wordt eerst het webbestand lokaal opgeslagen als 'G:\Q_test.accdb'.
Daarna worden alle gegevens uit Tabel 'Q_test' van bestand 'G:\Q_test.accdb' gelezen en in de ComboBox gezet.

Dim sp() As Byte
With CreateObject("MSXML2.XMLHTTP")

.Open "get", "http://www.snb-vba.eu/bestanden/Q_test.accdb", 0
.send
sp = .responsebody

End With

Open "G:\Q_test.accdb" For Binary As #1

Put 1, , sp

Close

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM Q_test", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Q_test.accdb"
ComboBox1.column = .getrows

End With

6.3.6.5 Excelbestand uit website

Met de methode GetObject kan het Excel webbestand niet geopend worden.
Met de methode CreateObject kun je het Excelbestand direkt vanuit de webpagina lezen.

With CreateObject("Excel.Application")

ComboBox1.List =.Workbooks.Open("http://www.snb-vba.eu/bestanden/Q_test.xlsx").Sheets(1).UsedRange.Value
.Quit

End With

6.3.6.6 Tekstbestand uit website

Om het tekstbestand als 2-dimensionele Array uit te lezen wordt het web-bestand eerst lokaal opgeslagen.

WithCreateObject("MSXML2.XMLHTTP")

.Open "get", "http://www.snb-vba.eu/bestanden/Q_test.csv", 0
.send
CreateObject("Scripting.FileSystemObject").CreateTextFile("G:\Q_test.csv").write .responsetext

End With

With CreateObject("ADODB.Recordset")

.Open "SELECT * FROM `Q_test.csv`", "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=G:\"
ComboBox1.Column = .getrows

End With

7 Bepaal of een waarde voorkomt in de ComboBox

7.1 eigenschap .Value

De ComboBox heeft geen aparte eigenschap/methode om te bepalen of de ComboBox een bepaald item bevat.
Met de eigenschap '.Value' kun je vaststellen of een ComboBox een bepaalde waarde bevat.
De VBA-code die je daarvoor moet gebruiken is afhankelijk van de '.style' eigenschap van de ComboBox.

ComboBox.style = 0-frmStyleDropDownCombo

Wanneer de waarde bestaat wordt die waarde geselecteerd en is de overeenkomstige .Listindex gewijzigd.
Als de waarde niet bestaat is de .Listindex waarde -1, als de waarde bestaat is de .Listindex groter dan -1.
Aan de hand van het indexnummer kun je dus nagaan of een bepaalde waarde bestaat.

ComboBox1.List = sheets(1).range("A1:K10").Value
ComboBox1.value ="aa22"

Msgbox "ComboBox1 bevat " & ComboBox1.value & " : " & Format(ComboBox1.Listindex>-1,"yes/no")

Er is 1 beperking: de eigenschap .Value werkt alleen voor tekenreeksen (strings).
Je kunt dus niet nagaan of een bepaald getal of datumwaarde in de ComboBox voorkomt.

ComboBox.style = 2-frmStyleDropDownList

Met deze style kan de gebruiker geen waarden invoeren in de ComboBox.
Een waarde kan alleen geselecteerd worden en moet altijd exact overeenkomen met een van de waarden die aan de ComboBox vooraf zijn toegekend.
Als een waarde niet bestaat wordt een foutcode gegenereerd.
Aan de hand van het vóórkomen van een foutcode kun je nagaan of een item een bepaalde waarde heeft in een ComboBox met de .style '2-frmStyleDropDownList'.

ComboBox1.List = sheets(1).range("A1:K10").Value
ComboBox1.Style = 2
c00="aa24"
On Error Resume Next

ComboBox1.Value = c00

Msgbox "ComboBox1 bevat " & c00 & " : " & Format(err.number=0, "yes/no")

Ook hier geldt de beperking dat de eigenschap .Value alleen voor tekenreeksen (strings) werkt.
Je kunt dus niet nagaan of de ComboBox een bepaald getal of datumwaarde bevat.

7.2 De Excel-methode Match

Dankzij het 'Excel'-karakter van een ComboBox kun je Excel-methoden/formules op de ComboBox toepassen.
De Excel-methode Match geeft aan in welke positie een bepaald item in een 1-dimensionele array voorkomt.
Als het item niet in de Array voorkomt wordt een foutcode gegenereerd.
Aan de hand van de foutcode kun je bepalen of een array een bepaald item bevat.
De methode Match werkt onafhankelijk van de '.style' van een ComboBox.
De methode Match werkt alleen binnen één rij of kolom.
De methode Match kan voor alle soorten gegevens (tekst, getallen, datums) gebruikt worden.
Met de Excel-methode Application.Index kun je aangeven in welke kolom naar de waarde moet worden gezocht.

Zoek een tekstreeks in kolom 1

ComboBox1.List = sheets(1).range("A1:K10").Value
c00="aa24"

Msgbox "ComboBox1 bevat " & c00 & " : " & Format(Not(IsError(Application.Match(c00, Application.Index(ComboBox1.List, 0, 1), 0))), "yes/no")

Zoek een getal in kolom 2

ComboBox1.List = sheets(1).range("A1:K10").Value
x = 3056

Msgbox "ComboBox1 bevat " & x & " : " & Format(Not(IsError(Application.Match(x, Application.Index(ComboBox1.List, 0, 2), 0))), "yes/no")

Zoek een datum in kolom 3
De methode match vindt alleen maar een datum in tekstvorm.

ComboBox1.List = sheets(1).range("A1:K10").Value
c00=date

Msgbox "ComboBox1 bevat " & c00 & " : " & Format(Not(IsError(Application.Match(Format(c00), Application.Index(ComboBox1.List, 0, 3), 0))), "yes/no")

In de ComboBox kun je instellen welke kolom gebruikt moet worden om de waarde van de ComboBox te bepalen.
Dit is natuurlijk alleen maar interessant voor 2-dimensionele (>1 kolom) ComboBoxen.
Met de eigenschap .BoundColumn leg je vast welke kolom bepalend is voor de waarde van de ComboBox.
Als je in dezelfde kolom wil zoeken die de ComboBox gebruikt voor het bepalen van de waarde:

ComboBox1.List = sheets(1).range("A1:K10").Value
c00="cc30"

Msgbox "ComboBox1 bevat " & ComboBox1.value & " : " & Format(Not(IsError(Application.Match(Format(c00), Application.Index(ComboBox1.List, 0, ComboBox1.BoundColumn), 0))), "yes/no")

8. De omvang van een ComboBox

De ComboBox is vergelijkbaar met een 2-dimensionele Array. De ondergrens van iedere dimensie (rij resp. kolom) is 0.

8.1 Het aantal items/rijen

8.1.1 Eigenschap .ListCount

De eigenschap .ListCount geeft het aantal items/rijen/records van/in een ComboBox weer.

ComboBox1.List = sheets(1).range("A1:K10").Value

Msgbox ComboBox1.Listcount

8.1.2 Eigenschap .List

Je kunt hiervoor ook de Ubound() eigenschap van de eigenschap .List gebruiken.
Omdat de LBound() van .List 0 is, moet je bij het resultaat 1 optellen om het aantal rijen weer te geven.

ComboBox1.List = sheets(1).range("A1:K10").Value

Msgbox Ubound(ComboBox1.List)+1

8.1.3 Eigenschap .Column

Omdat de eigenschap .Column de getransponeerde gegevens van de ComboBox weergeeft, moeten we bij .Column de ubound van de 2e dimensie (= rows) gebruiken.

ComboBox1.List = Sheets(1).Range("A1:K10").Value

Msgbox Ubound(ComboBox1.Column,2)+1

Houd er rekening mee dat de methode .additem een lege ComboBox vult met 1 item bestaande uit 10 kolommen.
Ook al zie je maar 1 item, de omvang is anders dan je vermoedt.

8.2 Het aantal kolommen

De ComboBox heeft geen eigenschap om het aantal kolommen van de gegevens in de ComboBox weer te geven.
De eigenschap .ColumnCount geeft aan hoeveel kolommen er in de ComboBox getoond worden.

8.2.2 Eigenschap .List

Met de bovengrens van de 2e dimensie van de eigenschap .List kun je het aantal kolommen van de gegevens in de ComboBox bepalen.
Omdat de ondergrens van de eigenschap 0 is, dien je 1 op te tellen bij het resultaat van Ubound() om het juiste aantal kolommen te krijgen.

ComboBox1.List = Sheets(1).Range("A1:K10").Value

Msgbox "aantal kolommen: " & Ubound(ComboBox1.List,2) + 1

8.2.3 Eigenschap .Column

Met de bovengrens van de 1e dimensie van de eigenschap .Column kun je het aantal kolommen van de gegevens in de ComboBox bepalen.
Omdat de ondergrens van de eigenschap 0 is, dien je bij het resultaat van Ubound() 1 op te tellen voor het juiste aantal kolommen.

ComboBox1.List = sheets(1).range("A1:K10").Value

Msgbox "aantal kolommen: " & Ubound(ComboBox1.Column) + 1

9. Lezen/ophalen van elementen

9.1 1 element

Aan de hand van het indexnummer kan de waarde van een element in de ComboBox worden gelezen.
Dat kan via de eigenschap .List of via de eigenschap .Column.

Houd er rekening mee dat:
- een ComboBox een 2-dimensionele Array is, zodat je altijd het rijnummer én het kolomnummer moet specificeren
- de ondergrens van de .List en de .Column eigenschap altijd 0 is

ComboBox1.List=Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")

Msgbox ComboBox1.List(0,0)
Msgbox ComboBox1.List(Ubound(ComboBox1.List),0)
Msgbox ComboBox1.List(ComboBox1.Listcount)-1,0)

' het eerste item
' het laatste item
' het laatste item

9.2 verschillende elementen: filteren

De ComboBox kent geen eigen filtermethode.

9.2.1 filter een ComboBox met 1 rij of 1 kolom

Als de ComboBox slechts 1 kolom of 1 rij met gegevens bevat kun je gebruik maken van de VBA-methode 'Filter' voor 1-dimensionele Arrays.
Het resultaat van de filtering is een 1-dimensionele Array die aan de ComboBox kan worden toegeschreven.
Ook al bevat een ComboBox slechts 1 rij of 1 kolom met gegevens, dan nog is de ComboBox een 2-dimensionele Array.
Als je de methode 'Filter' wil gebruiken zul je dus eerst de 2-dimensionele Array van de ComboBox moeten transponeren naar een 1-dimensionele Array.
Dat kan met de Excel-methoden 'Index' of 'Transpose'.
Daarna kun je de Filtermethode toepassen op de zo ontstane 1-dimensionele Array.

filter alle rijen die de tekst 'bb' bevatten

ComboBox1.List = Array("aa1", "aa2", "bb3", "bb4", "bb5", "cc6", "cc7")
ComboBox1.List = Filter(Application.Transpose(ComboBox1.List), "bb")

ComboBox1.List = Array("aa1", "aa2", "bb3", "bb4", "bb5", "cc6", "cc7")
ComboBox1.List = Filter(Application.Index(ComboBox1.Column,1,0), "bb")

filter alle kolommen die de tekst 'bb' bevatten

ComboBox1.Column = Array("aa1", "aa2", "bb3", "bb4", "bb5", "cc6", "cc7")
ComboBox1.Column = Filter(Application.Transpose(ComboBox1.Column), "bb")

ComboBox1.Column = Array("aa1", "aa2", "bb3", "bb4", "bb5", "cc6", "cc7")
ComboBox1.Column = Filter(Application.Index(ComboBox1.List, 1, 0), "bb")

9.2.2 filter 1 rij of 1 kolom van een ComboBox met meer rijen/kolommen

De VBA-methode Filter kun je ook gebruiken als je slechts 1 rij of kolom van een ComboBox wil filteren.
Het resultaat is altijd een 1-dimensionele Array.
Het verband met de gegevens in de overige kolommen/rijen gaat door de filtering verloren.
filter de tweede kolom van de ComboBox op het voorkomen van 'bb3'

ComboBox1.List = Cells(1).CurrentRegion.Value
Msgbox Join(Filter(Application.Transpose(Application.Index(ComboBox1.List, 0, 2)), "bb3"), vbLf)

filter de derde rij van de ComboBox op het voorkomen van 'bb3'

ComboBox1.List = Cells(1).CurrentRegion.Value
Msgbox Join(Filter(Application.Index(ComboBox1.List, 3), "bb3"), vbLf)

9.3 filteren van een meer-dimensionele ComboBox

Als je een ComboBox wil filteren met behoud van het verband met andere kolommen zul je gebruik moeten maken van het Excel-karakter van de ComboBox.
Met de Excelfunktie 'Index' kun je aangeven welke elementen (rijen) en/of welke kolommen moeten worden gefilterd.

De Excelfunktie 'Index' heeft 3 argumenten:
- de array die gefilterd moet worden; in dit geval de ComboBox
- een 2-dimensionele array met de indexnummers van de te filteren rijen/records
- een 1-dimensionele array met de indexnummers van de te filteren kolommen/velden

Wees erop bedacht dat de ondergrens van 'Index' voor zowel rijen als kolommen 1 is. In een ComboBox is de ondergrens van rijen en kolommen 0.

9.3.1 filter alle gegevens uit 1 kolom

Als je de gegevens uit bijv. de 2e kolom wil filteren hoef je de te filteren elementen (rows) niet te specificeren:

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List=Application.Index(ComboBox1.List,0,2)

of anders geschreven

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List=Application.Index(ComboBox1.List,,2)

Als je gebruik wil maken van de .Column eigenschap van de ComboBox, moet je de argumenten voor rijen en kolommen omwisselen:

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List=Application.Index(ComboBox1.Column,2,0)

of anders geschreven

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List=Application.Index(ComboBox1.Column,2)

9.3.2 filter alle gegevens uit 2 of meer kolommen

Als je gegevens uit meer kolommen wil filteren moet je zowel de 'records' (rijen) als de kolommen specificeren.
De indexnummers van de te filteren records" Daarvoor heb je een 2-dimensionele array nodig waarin de indexnummers van de items staan.
De Excelfunktie Index heeft als ondergrens 1, dat is dan ook de ondergrens voor de waarde van de 2-dimensionele array.
Er zijn verschillende methoden om een 2-dimensionele array met alle indexnummers te maken.
Hier met een loop en met de methode Evaluate in Excel

Voorbeeld: alle gegevens uit kolom 1 en 3 van de ComboBox

variabele en een lus

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value

ReDim sp(1 ToComboBox1.Listcount,0)
For j=1 ToComboBox1.Listcount

sp(j,0)=j

Next

ComboBox1.List=Application.Index(ComboBox1.List,sp,Array(1,3))

methode Evaluate (Excel)

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List=Application.Index(ComboBox1.List,evaluate("row(1:" & ComboBox1.Listcount & ")"),Array(1,3))

9.3.3 filter alle gegevens van 2 of meer items

Als je gebruik maakt van de eigenschap .List van de ComboBox, specificeer je in een 2-dimensionele array welke items je wil filteren.
In een 1-dimensionele array specificeer je welke kolommen je gefilterd wil hebben.
Als je tevoren weet hoeveel kolommen de ComboBox bevat kun je de methode Array(..,..,..) of Split gebruiken.
Als dat onbekend is kun je een lus gebruiken of de Excel methode Evaluate.

methode Array

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List=Application.Index(ComboBox1.List,Application.Transpose(Array(1,3,5)),Array(1,2,3,4))

methode Split

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List=Application.Index(ComboBox1.List,Application.Transpose(Array(1,3,5)), Split("1 2 3 4"))

variabele en een lus

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value

ReDim sp(Ubound(ComboBox.List,2))
For j=0 To Ubound(sp)

sp(j) = j + 1

Next

ComboBox1.List=Application.Index(ComboBox1.List, Application.Transpose(array(1,3,5)),sp))

methode Evaluate (Excel)

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value

ComboBox1.List = Application.Index(ComboBox1.List, Application.Transpose(Array(1, 3, 5)), Evaluate("Transpose(row(1:" & Ubound(ComboBox1.List, 2) + 1 & "))"))

9.3.4 filter bepaalde kolommen van bepaalde items

Met de Excel methode 'Index' kun je gegevens uit een ComboBox filteren op rij (item/record) en kolom (veld).
Je kunt de filter toepassen op de .List eigenschap of op de .Column eigenschap van de ComboBox.
Rijen filter je op basis van een 2-dimensionele array en kolommen op basis van een 1-dimensionele array.
In die arrays komen de indexnummers van de rijen respectievelijk kolommen te staan.
De eigenschap .List kent als tweede argument de 2-dimensionele array met te selecteren rijen en als derde argument de 1-dimensionele array met te selecteren kolommen.
Bij de eigenschap .Column zijn de argumenten precies andersom: het tweede argument bevat de 1-dimensionele array voor de kolommen, het derde argument de 2-dimensionele array voor de rijen.

Bijvoorbeeld: filter de gegevens in de kolommen 1,3 en 4 uit items 2,7 en 12

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List=Application.Index(ComboBox1.List,Application.Transpose(Array(2,7,12)), Array(1,3,4))

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List=Application.Index(ComboBox1.Column, Array(1,3,4), Application.Transpose(Array(2,7,12)))

10 wijzig een element in de ComboBox

De gebruiker kan in een ComboBox geen wijzigingen in ingelezen items aanbrengen.
Je kunt met VBA wél ieder element in een ComboBox wijzigen.
Welke methode je daarvoor kunt gebruiken is afhankelijk van de .Listindex van de ComboBox.
Als een element is geselecteerd kun je nl. gebruik maken van de eigenschap .Column; in andere gevallen niet.

10.1 wijzig een element onafhankelijk van de .Listindex

10.1.1 eigenschap .List

Met de eigenschap .List kun je elk element van de ComboBox afzonderlijk benaderen.
Het eerste argument van de eigenschap .List geeft de 'rij' (het record) van de ComboBox weer.
Het tweede argument van de eigenschap .List geeft de 'kolom' (het veld) van de ComboBox weer.
De 1e rij van een ComboBox heeft als indexnummer 0.
De 1e kolom van een ComboBox heeft als indexnummer 0.

Wijzig het gegeven in de 1e kolom van de 1e rij van de ComboBox

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List(0,0)="anders"

Wijzig het gegeven in de laatste kolom van de laatste rij van de ComboBox

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List(Ubound(ComboBox1.List), Ubound(ComboBox1.List,2))="anders"

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List(ComboBox1.Listcount-1, Ubound(ComboBox1.List,2))="anders"

With ComboBox1

.List=Sheet1.Cells(1).CurrentRegion.Value
.List(Ubound(.List), Ubound(.List,2))="anders"

End With

Wijzig het gegeven in de 3e kolom van de 6e rij van de ComboBox

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List(5,2)="anders"

10.1.2 eigenschap .Column

Met de eigenschap .Column kun je elk element van de ComboBox afzonderlijk benaderen.
Het eerste argument van de eigenschap .Column geeft de 'kolom' (het veld) van de ComboBox weer.
Het tweede argument van de eigenschap .List geeft de 'rij' (het record) van de ComboBox weer.
De 1e rij van een ComboBox heeft als indexnummer 0.
De 1e kolom van een ComboBox heeft als indexnummer 0.

Wijzig het gegeven in de 1e kolom van de 1e rij van de ComboBox

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.Column(0,0)="anders"

Wijzig het gegeven in de laatste kolom van de laatste rij van de ComboBox

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.Column(Ubound(ComboBox1.Column), Ubound(ComboBox1.List))="anders"

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.Column(Ubound(ComboBox1.List,2),ComboBox1.Listcount-1)="anders"

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.Column(Ubound(ComboBox1.Column), Ubound(ComboBox1.Column,2))="anders"

With ComboBox1

.List=Sheet1.Cells(1).CurrentRegion.Value
.Column(Ubound(.Column), Ubound(.Column,2))="anders"

End With

Wijzig het gegeven in de 3e kolom van de 6e rij van de ComboBox

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.Column(2,5)="anders"

10.2 wijzig een geselecteerd element in de ComboBox

Als een element in de ComboBox geselecteerd is, heeft de .Listindex een waarde groter dan -1.
De eigenschap .Listindex geeft het indexnummer weer van het geselecteerde item (rij/record).
Met de eigenschap .Column kun je dan de kolomwaarde van het geselecteerde element wijzigen.
De eerste kolom heeft indexnummer 0.

Wijzig het gegeven in de 1e kolom van het geselecteerde item

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.Listindex=3
ComboBox1.Column(0)="anders"

Wijzig het gegeven in de laatste kolom van het geselecteerde item

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.Listindex=3
ComboBox1.Column(Ubound(ComboBox1.Column))="anders"

Wijzig het gegeven in de 3e kolom van het geselecteerde item

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.Listindex=3
ComboBox1.Column(2)="anders"

11 Transponeer ComboBox-gegevens

De ComboBox heeft 2 methodes om gegevens in- en uit te lezen: .List en .Column.
Als je de ene methode gebruikt om gegevens in te lezen, kun je de andere methode gebruiken om ze getransponeerd uit te lezen.
Deze methode heeft niet de beperking van de funktie 'Transpose' (Application.Transpose) in Excel.

Lees een 3*10 array in; lees hem als 10*3 array uit.

ReDim sn(2,9)
Msgbox Ubound(sn) & vbtab & Ubound(sn,2)

ComboBox1.List=sn

sn=ComboBox1.Column
Msgbox Ubound(sn) & vbtab & Ubound(sn,2)

Je kunt zo zelfs heel eenvoudig de gegevens in de ComboBox zelf transponeren:

ReDim sn(2,9)
ComboBox1.List=sn

ComboBox1.Column=ComboBox1.List

ReDim sn(2,9)
ComboBox1.List=sn

ComboBox1.List=ComboBox1.Column

Dat kan van pas komen als je een 2-dimensionele array met slechts 1 record in een ComboBox inleest.
De ComboBox transponeert die nl. automatisch en ongewenst.

Zo kun je dat oplossen:

sn = Sheet1.Cells(1).CurrentRegion
ComboBox1.List = Application.Index(sn, 1, 0)

If Ubound(ComboBox1.Column)= 0 Then ComboBox1.List = ComboBox1.Column

12 ComboBoxgegevens schrijven

De gegevens in een ComboBox gedragen zich als een 2-dimensionale array.
Als je ze verder wil bewerken/opslaan kun je ze als 2-dimensionele array behandelen.

12.1 ComboBoxgegevens in een Array zetten

De array variabele waarnaar de gegevens worden geschreven is automatisch van het Variant, Array type.

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
sn=ComboBox1.List

12.2 ComboBoxgegevens getransponeerd in een Array zetten

De array variabele waarnaar de gegevens worden geschreven is automatisch van het Variant, Array type.

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
sn=ComboBox1.Column

12.3 ComboBoxgegevens in een Excel werkblad schrijven

Bij het schrijven van ComboBoxgegevens naar een werkblad is omvangdefinitie van het doelgebied vereist.
Houd daarbij rekening met het gegeven dat de ondergrens van zowel de rijen als de kolommen 0 is.
Tel voor de juiste dimensionering van het doelgebied bij de ubound-eigenschap van zowel de rij als de kolom 1 op.
Als het gedefinieerde doelgebied groter is dan de omvang van de ComboBox, verschijnt in die cellen de foutcode #/NA.

ComboBox1.List = Sheet1.Cells(1).CurrentRegion.Value
sheet2.Cells(1).Resize(Ubound(ComboBox1.List)+1,Ubound(ComboBox1.Column) +1) = ComboBox1.List

Je kunt een deel van de ComboBox wegschrijven door het doelgebied kleiner te definiëren dan de omvang van de ComboBox.
Als je alleen de eerste 4 rijen en eerste 4 kolommen van de ComboBox wil wegschrijven:

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
sheet2.Cells(1).Resize(4,4)=ComboBox1.List

12.4 ComboBoxgegevens in een Dictionary zetten

Je kunt een volledige ComboBox als item in een dictionary opnemen:

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value

With CreateObject("scripting.dictionary")

.Item("snb")=ComboBox1.List

End With

Als je afzonderlijke items uit de ComboBox als afzonderlijke items in de Dictionary wil zetten:

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value

With CreateObject("scripting.dictionary")

Forj=0 To ComboBox1.Listcount-1

.Item(.Count)=Application.Index(ComboBox1.List,j+1,0)

End With

Wanneer je de gegevens van twee ComboBoxen wil samenvoegen in bijv. ComboBox3:

ComboBox1.List=Sheet1.Cells(1).CurrentRegion.Value
ComboBox2.List=Sheet2.Cells(1).CurrentRegion.Value

With CreateObject("scripting.dictionary")

For j=0 To Ubound(ComboBox1.List)

.Item(.Count)=Application.Index(ComboBox1.List,j+1,0)

Next
For j=0 To Ubound(ComboBox2.List)

.Item(.Count)=Application.Index(ComboBox2.List,j+1,0)

Next
ComboBox3.List=Application.Index(.Items,0,0)

End With

13. sorteren van elementen

De ComboBox kent geen eigen sorteermethode.
Als je gegevens in een ComboBox wil sorteren zul je gebruik moeten maken van de sorteermethode van een andere VBA bibliotheek of een eigen methode. Na sortering daar moet het resultaat weer in de ComboBox gelezen worden.
Voor de keuze van een sorteermethode is van belang of de ComboBox een enkele gegevenskolom bevat of diverse kolommen.

13.1 sorteren van 1 gegevenskolom

Er zijn diverse VBA sorteermethodes.
Ter illustratie hier de volgende 4 methoden:
- een Excel werkblad
- een Arraylist
- een Sortedlist
- een ADODB recordset

13.1.1 Excel werkblad

Zet de inhoud van de ComboBox in een gebied in een werkblad dat overeenkomt met de omvang van de ComboBox.
Sorteer het gebied met de Excelmethode 'Sort'.
Lees het gesorteerde gebeid in in de ComboBox.

oplopend sorteren

Sheet1.Cells(1, 20).Resize(Ubound(ComboBox1.List), 1) = ComboBox1.List
Sheet1.Cells(1, 20).CurrentRegion.Sort Sheet1.Cells(1, 20)

ComboBox1.List = Sheet1.Cells(1, 20).CurrentRegion.Value

aflopend sorteren

With Sheet1.Cells(1, 20)

.Resize(Ubound(ComboBox1.List), 1) = ComboBox1.List
.CurrentRegion.Sort Sheet1.Cells(1, 20), 2

ComboBox1.List = .CurrentRegion.Value

End With

13.1.2 Arraylist

Voor een correcte sortering is vereist dat de kolomgegevens van hetzelfde gegevenstype (tekst of getal) zijn.

oplopend sorteren

With CreateObject("system.collections.arraylist")

For j=0 To Ubound(ComboBox1.List)

.Add ComboBox1.List(j,0)

Next
.Sort

ComboBox1.List = .ToArray

End With

aflopend sorteren

With CreateObject("system.collections.arraylist")

For j=0 To Ubound(ComboBox1.List)

.Add ComboBox1.List(j,0)

Next
.Sort
.Reverse

ComboBox1.List = .ToArray

End With

13.1.3 Sortedlist

Een Sortedlist sorteert alle gegevens van eenzelfde gegevenstype altijd oplopend op de sleutel.
Wil je gegevens aflopend in de ComboBox hebben, dan zul je het sorteerresultaat in omgekeerde volgorde moeten inlezen.

oplopend sorteren

With CreateObject("system.collections.sortedlist")

For j=0 To Ubound(ComboBox1.List)

.Add ComboBox1.List(j,0),""

Next

For j=0 To .Count-1

ComboBox1.List(j,0) = .GetKey(j)

End With

aflopend sorteren

With CreateObject("system.collections.sortedlist")

For j=0 To Ubound(ComboBox1.List)

.Add ComboBox1.List(j,0),""

Next

For j=0 To .Count-1

ComboBox1.List(j,0) = .GetKey(.Count - 1 - j)

End With

13.1.4 ADODB recordset

De ADODB-recordset geeft een 'stabiele' sortering.
Bij elementen met dezelfde waarde blijft de volgorde waarin de elementen aan de recordset zijn toegevoegd behouden.
Het is een van de snelste VBA sorteermethoden.

Maak eerst een sorteerveld aan in de recordset: in het voorbeeld het veld 'sorteer'.
Het tweede argument geeft aan dat het om een tekstveld (129) gaat van 60 tekens lang (het derde argument).
Een lus voegt nieuwe 'records' toe. Ieder record krijgt 1 waarde uit de ComboBox toegewezen in het veld 'sorteer'.
In de methode .Sort staat de naam van het sorteerveld en de sorteermethode: oplopend of aflopend.
De gesorteerde gegevens komen met .Getrows in de ComboBox te staan.
Gebruik daarvoor de eigenschap ComboBox1.Column, omdat .Getrows de gegevens transponeert.

oplopend sorteren

With CreateObject("ADODB.recordset")

.Fields.Append "sorteer", 129, 60
.Open
For j = 0 To Ubound(ComboBox1.List)

.Addnew
.Fields("sorteer") = ComboBox1.List(j, 0)
.Update

Next
.Sort = "sorteer Asc"

ComboBox1.Column = .getrows

End With

aflopend sorteren

With CreateObject("ADODB.recordset")

.Fields.Append "sorteer", 129, 60
.Open
For j = 0 To Ubound(ComboBox1.List)

.Addnew
.Fields("sorteer") = ComboBox1.List(j, 0)
.Update

Next
.Sort = "sorteer Desc"

ComboBox1.Column = .getrows

End With

13.2 sorteren op verschillende kolommen

Het aantal VBA sorteermethodes op diverse kolommen is beperkt.
- in een Excel werkblad
- in een ADODB recordset

13.2.1 Excel werkblad

In dit voorbeeld sortering op de eerste kolom, vervolgens de derde kolom en tenslotte de vijfde kolom.
Sinds Office 2010 kun je met sortfilters op meer dan 3 kolommen sorteren.

oplopend sorteren

With Sheet1.Cells(1, 20)

.Resize(Ubound(ComboBox1.List) + 1, Ubound(ComboBox1.Column) + 1) = ComboBox1.List
.CurrentRegion.Sort .Offset, 1, .Offset(,2), , 1, .Offset(,4), 1

ComboBox1.List = .CurrentRegion.Value

End With

aflopend sorteren

With Sheet1.Cells(1, 20)

.Resize(Ubound(ComboBox1.List) + 1, Ubound(ComboBox1.Column) + 1) = ComboBox1.List
.CurrentRegion.Sort .Offset, 2, .Offset(,2), , 2, .Offset(,4), 2

ComboBox1.List = .CurrentRegion.Value

End With

13.2.2 ADODB-recordset

oplopend sorteren

With CreateObject("ADODB.recordset")

.Fields.Append "sort_1", 129, 60
.Fields.Append "sort_2", 129, 60
.Fields.Append "sort_3", 129, 60
.Open

For j = 0 To Ubound(ComboBox1.List)

.Addnew
.Fields("sort_1") = ComboBox1.List(j, 0)
.Fields("sort_2") = ComboBox1.List(j, 2)
.Fields("sort_3") = ComboBox1.List(j, 4)
.Update

Next
.Sort = "sort_1, sort_2, sort_3 Asc"

ComboBox1.Column = .getrows

End With

aflopend sorteren

With CreateObject("ADODB.recordset")

.Fields.Append "sorteer", 129, 60
.Fields.Append "sort_2", 129, 60
.Fields.Append "sort_3", 129, 60
.Open

For j = 0 To Ubound(ComboBox1.List)

.Addnew
.Fields("sort_1") = ComboBox1.List(j, 0)
.Fields("sort_2") = ComboBox1.List(j, 2)
.Fields("sort_3") = ComboBox1.List(j, 4)
.Update

Next
.Sort = "sort_1, sort_2, sort_3 Desc"

ComboBox1.Column = .getrows

End With

14. verwijder elementen uit een ComboBox

14.1 1 element

De methode .RemoveItem verwijdert 1 element ('rij'/'record') uit de ComboBox.
In het argument geef je het indexnummer van het element op.
Als een record diverse kolommen heeft worden ook alle gegevens in die kolommen tegelijkertijd verwijderd.
Wees erop bedacht dat iedere verwijdering van een element uit een ComboBox alle indexnummers in de ComboBox herschikt.
Na iedere verwijdering hebben alle elementen met een hoger indexnummer een nieuw indexnummer gekregen.

Verwijder het eerste item

ComboBox1.List=Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")
ComboBox1.RemoveItem 0

Verwijder het laatste item

ComboBox1.List=Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")
ComboBox1.RemoveItem Ubound(ComboBox1.List)

ComboBox1.List=Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")
ComboBox1.RemoveItem ComboBox1.Listcount - 1

Verwijder het 3e item

ComboBox1.List=Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")
ComboBox1.RemoveItem 2

14.2 diverse elementen

Als je verschillende elementen uit de ComboBox wil verwijderen doe je dat het beste van achteren naar voren (of van onder naar boven), startend van Ubound naar LBound.
Zo interfereert de herindexatie niet met de verwijderingsoperatie.

Verwijder het 7e, 5e en 3e element

ComboBox1.List = Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6","bb7")
For j = 6 To 2 step -2

ComboBox1.RemoveItem j

Hiervoor kun je in Excel ook de filtermethode gebruiken.
Door de specificatie van te filteren records geef je tegelijkertijd de te verwijderen records aan.
Om records 3, 5 en 7 te verwijderen, moet je records 1,2,4 en 6 filteren.

Verwijder het 7e, 5e en 3e element

ComboBox1.List = Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List=Application.Index(ComboBox1.List,Application.Transpose(Array(1,2,4,6)),Array(1,2,3,4,5))

Je kunt niet alleen bepaalde items verwijderen, maar ook bepaalde kolommen
Verwijder het 7e, 5e en 3e element; en verwijder kolom 1 en 3

ComboBox1.List = Sheet1.Cells(1).CurrentRegion.Value
ComboBox1.List=Application.Index(ComboBox1.List,Application.Transpose(Array(1,2,4,6)),Array(2,4,5))

14.3 verwijder alle elementen

De methode .Clear verwijdert alle elementen uit de ComboBox.
Deze methode hoef je alleen maar te gebruiken als je elementen toevoegt met de methode AddItem.
Als je een ComboBox vult met de eigenschap .List of .Column is .clear overbodig, omdat deze 2 methoden de volledige inhoud van de ComboBox vervangen.

ComboBox1.List =Array("aa1", "aa2", "aa3", "aa3", "aa2", "aa6")
ComboBox1.Clear