Home > Excel VBA makroer > ListBoxe

ListBoxe på userforms/formularer med Excel VBA

Listboxe bruges ofte som kontrolelementer på userforms/formularer. De viser en liste med værdier, og brugeren kan så vælge én eller flere. Brugerens valg afgør så, hvad man videre foretager sig.

På denne side er der eksempler på, hvordan du udfylder og håndterer ListBoxe på dine egne userforms. Jeg viser også, hvordan man kan forvælge værdier på listen. Koden kan markeres og kopieres (CTRL+C og CTRL+V) ind i formularens kode.

Bruger du en lille skærm, kan kodelinjer være tekstombrudte, men linjeskift bliver OK, når du indsætter det kopierede.

For ComboBoxe gælder stort set det samme som for ListBoxe, så dem springer jeg over eller tager en anden gang.

Hvis du vil lege med hen ad vejen, er det måske en god idé at åbne Excel og VBA-editoren (ALT+F11) og indsætte en ny userform. Tilføj en ListBox og en kommandoknap. Markér formularen og tryk F7 for at åbne dens kodevindue. Så er du klar.

Hvordan man udfylder en ListBox

En liste kan udfyldes ved at bruge celler i regnearket som kilde - listens RowSource - eller ved at tilføje værdierne én efter én med AddItem metoden.

Hvis det er datoer, kan det være drilsk at få dem vist i det ønskede format - læs mere herom på Datovisning i Combo- og ListBoxe.

RowSource som kilde

En af ListBoxens properties er "RowSource", som er adressen på et range i regnearket, f.eks. "Ark1!A1:A15".

Listens RowSource kan skrives direkte i ListBoxens properties vindue (tryk F4 hvis vinduet ikke er vist i VBA-editoren), eller området kan defineres i formularens Initialize procedure.

Det er vigtigt at vide, at RowSource altid er af datatypen String, altså tekst, og at den henviser til det aktive ark, medmindre andet angives.

Hvis man angiver RowSource i formularens Initialize procedure, kan det f.eks. se sådan ud:

Private Sub UserForm_Initialize()
   ListBox1.RowSource = "Ark1!A1:A15"
End Sub

Man kan også bruge et navngivet område i sit regneark. Hvis området f.eks. har navnet "kilde", ser det således ud:

Private Sub UserForm_Initialize()
   ListBox1.RowSource = Range("kilde").Address
End Sub

Bemærk, at vi skal have .Address med i enden for at få en string.

Dynamisk range som RowSource

Ovenstående fremgangsmåde fungerer fint, hvis det altid er værdierne i et bestemt område, man skal bruge. Men hvad nu hvis det er et område, som kan have et varierende antal rækker fra gang til gang?

Så må man bruge et dynamisk range som RowSource i sin Initialize procedure. I det følgende eksempel finder vi antallet af rækker ned til den første tomme celle og bruger dem som RowSource.

Private Sub UserForm_Initialize()
Dim rRange As Range

On Error GoTo ErrorHandle

'Vi sætter vores range til celle A1 i Ark1
Set rRange = Worksheets("Ark1").Range("A1")

'Kontrollerer om cellen er tom
If Len(rRange.Formula) = 0 Then
   MsgBox "Listen er tom"
   GoTo BeforeExit
End If

'Finder næste tomme række og udvider rRange
If Len(rRange.Offset(1, 0).Formula) > 0 Then
   Set rRange = Range(rRange, rRange.End(xlDown))
End If

'Adressen på vores range bruges som RowSource
ListBox1.RowSource = rRange.Address

BeforeExit:
Set rRange = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

Udfylde listen med AddItem

I stedet for at angive en RowSource, hvor listens elementer indlæses fra regnearket, kan man tilføje elementerne ét for ét med AddItem-metoden. Man kan gøre det manuelt eller med en løkke, hvis man indlæser fra et range, en collection eller lignende.

Først et eksempel på den manuelle metode:

Private Sub UserForm_Initialize()

On Error GoTo ErrorHandle

With ListBox1
   .AddItem "Linje 1"
   .AddItem "Linje 2"
   .AddItem "Linje 3"
   .AddItem "Linje 4"
End With

Exit Sub
ErrorHandle:
MsgBox Err.Description
End Sub

Det er der jo ingen ben i. Man kunne gøre nøjagtig det samme med en løkke:

Private Sub UserForm_Initialize()
Dim lCount As Long

On Error GoTo ErrorHandle

For lCount = 1 To 4
   ListBox1.AddItem "Linje " & lCount
Next

Exit Sub
ErrorHandle:
MsgBox Err.Description
End Sub

Man kan også gennemløbe et range og bruge AddItem-metoden. Det er langsommere end at bruge områdets adresse som RowSource, men hvis det kun er bestemte ting, man vil have på sin liste, er det en fin måde. I følgende eksempel gennemløbes et dynamisk range i kolonne A. Forestil dig, at cellerne indeholder en blandet landhandel af tekst, datoer og tal, og at du kun vil have datoerne med på din liste:

Private Sub UserForm_Initialize()
Dim rRange As Range
Dim rCell as range

On Error GoTo ErrorHandle

'Vi sætter vores range til celle A1 i Ark1
Set rRange = Worksheets("Ark1").Range("A1")

'Kontrollerer om cellen er tom
If Len(rRange.Formula) = 0 Then
   MsgBox "Listen er tom"
   GoTo BeforeExit
End If

'Finder næste tomme række og udvider rRange
If Len(rRange.Offset(1, 0).Formula) > 0 Then
   Set rRange = Range(rRange, rRange.End(xlDown))
End If

'Nu gennemløber vi vores range, og
'kun datoer kommer med på listen
For Each rCell In rRange
   If IsDate(rCell.Value) Then
      ListBox1.AddItem rCell.Value
   End If
Next

BeforeExit:
Set rRange = Nothing
Set rCell = Nothing

Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

Sætte listboxens liste = et array

Når man bruger AddItem-metoden, kan kilden selvfølgelig også være et array, men man kan faktisk indsætte et array som liste i ét hug. Man sætter simpelthen listen = arrayet. Det minder meget om RowSource-metoden.

Nedenstående procedure udfylder først et array med tallene fra 1 til 100 og befolker dernæst listboxen - det er helt enkelt.

Sub ArrayToListBox()
Dim lCount As Long
Dim myArray(1 To 100, 1 To 1) As Long

'Udfyld arrayet
For lCount = 1 To 100
   myArray(lCount, 1) = lCount
Next

'Sæt listen = arrayet
With UserForm1
   .ListBox1.List = myArray
   .Show
End With

End Sub

Det kan også være en liste med flere kolonner. I nedenstående eksempel udfyldes et array med 2 kolonner, og det bruges derefter som kilde til listboxens liste.

Sub Array2ToListBox()
Dim lCol1 As Long
Dim lCol2 As Long
Dim myArray(1 To 100, 1 To 2) As Long

'Udfyld arrayet
For lCol1 = 1 To 100
   lCol2 = lCol2 + 4
   myArray(lCol1, 1) = lCol1
   myArray(lCol1, 2) = lCol2
Next

With UserForm1
   .ListBox1.ColumnCount = 2  'Sæt listen til 2 kolonner
   .ListBox1.List = myArray   'Sæt listen = arrayet
   .Show
End With

End Sub

En ListBox uden dubletter

Hvis man vil have en liste uden dubletter (og måske endda sorteret alfabetisk) med et område i regnearket som kilde, starter man med at lave en collection, som beskrevet nederst på siden Egne collections i Excel VBA (Avanceret collection).

Lad os sige, at vi har døbt vores collection "colListe". Så laver vi vores liste som herunder. For at få koden til at virke, skal du deklarere colListe som collection og skrive den procedure (her "MakeCollection"), som laver vores collection.

Private Sub UserForm_Initialize()
Dim lCount As Long

'Kalder proceduren som laver vores collection
MakeCollection

'Udfylder listen
With colListe
   For lCount = 1 To .Count
      ListBox1.AddItem .Item(lCount)
   Next
End With

Exit Sub
ErrorHandle:
MsgBox Err.Description
End Sub

Forvælg værdier i en Listbox

Der kan være situationer, hvor man gerne vil forvælge værdier. Det kan fx være for at markere, at disse værdier er valgt lige nu, men er med på listen, så de kan fravælges.

Det kan man gøre ved at gennemløbe listen og sætte de valgte værdiers Selected property = True. I det følgende eksempel indsætter vi tallene 1 til 10, og hvis tallet 2 går op i værdien, vælger vi den.

Private Sub UserForm_Initialize()
'Formularens Initialize-procedure
'afvikles inden formularen åbnes.
Dim lCount As Long

'Som altid, når vi opererer gentagne gange på
'et objekt, bruger vi With...End With
'konstruktionen for at spare tid.
With ListBox1
   'Angiver at der kan vælges mere end en værdi
   .MultiSelect = fmMultiSelectMulti
   
   'Bestemmer listens udseende. Her at der skal
   'være små afkrydsningsfelter til "flueben".
   .ListStyle = fmListStyleOption
   
   'Med tælleren lCount tilføjer vi tallene fra
   '1 til 10.
   For lCount = 1 To 10
      .AddItem lCount
   Next
   
   'Nu gennemløbes listen, og hver værdi
   'divideres med 2. Hvis resten er 0,
   'vælges værdien.
   'Da listen er et array startende med
   'index 0, gennemløber vi fra 0 til
   'antal værdier minus 1.
   '".List(lCount)" returnerer den enkelte
   'værdi, som også kunne være tekst.
   For lCount = 0 To .ListCount - 1
      If .List(lCount) Mod 2 = 0 Then
         .Selected(lCount) = True
      End If
   Next
End With

End Sub

Ovenstående vil se således ud, når dialogboksen åbner:

Hvis man har et array eller et range med True/False- eller talværdier, kan man også sætte listboxens Selected-property = værdien i sit array/range. "True" vil forvælge værdien, og det samme vil et tal forskelligt fra 0 (nul). Det kunne se således ud, hvor rRange er et range, hvor cellerne indeholder True/False eller tal:

   For lCount = 0 To .ListCount - 1
      .Selected(lCount) = rRange.Item(lCount + 1).Value
   Next

Når brugeren har valgt

Når brugeren har truffet sit valg, skal vi finde ud af, hvad han har valgt. Her er det afgørende, om ListBoxen er sat op til kun ét valg eller flere. Det valg træffer man i ListBoxens properties vindue under "MultiSelect", men man kan også sætte denne property i runtime med

ListBox1.MultiSelect = fmxxxx (3 valgmuligheder)

Hvis ListBoxen er sat op til, at brugeren kun kan vælge én ting, er det så nemt. Så finder man hans valg med:

ListBox1.Value

Hvis ListBoxen er sat op, så brugeren kan vælge flere ting, kan man ikke bruge ListBox1.Value, men skal i stedet have fat i ListBoxens Selected property.

Her følger et eksempel på en kommandoknap, hvor listen gennemløbes, og de valgte elementer indsættes i celle B1 og nedefter.

Bemærk at der i løkkens første linje står "-1" til sidst:

For lCount = 0 To .ListCount - 1

Det er fordi listens første element ikke hedder nr. 1, men derimod "0" (ligesom i arrays). Hvis du f.eks. har 10 elementer på din liste, returnerer "ListBox1.Count" ganske rigtigt tallet 10, men når du gennemløber listen fra 0 af, ja så er der 10 stk., når du når til 9 - prøv selv, hvis du har 10 fingre :-)

Private Sub CommandButton1_Click()
Dim rRange As Range
Dim lCount As Long   'Tæller

On Error GoTo ErrorHandle

'Celle B1 vælges som indsætningscelle
Set rRange = Range("B1")

'Listen gennemløbes, og valgte elementer
'indsættes i celle B1 og nedefter.
With ListBox1
   For lCount = 0 To .ListCount - 1
      If .Selected(lCount) = True Then
         rRange.Offset(lCount, 0).Value = .List(lCount)
      End If
   Next
End With

BeforeExit:
Set rRange = Nothing
Unload Me

Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

Relateret

• Formularer og dialogbokse
• Afhængige comboboxe, arrays i en class collection
• Datovisning i Combo- og ListBoxe
• TextBoxe på userforms
• Gennemløb af kontrolelementer på en userform
• Arrays
• Egne collections
• Sideoversigt