VBA-Steuerelemente: Eine ComboBox anlegen, füllen und verwenden

Von den Steuerelementen habe ich Dir bisher den SpinButton (Drehfeld) und die TextBox (Textfeld) erklären dürfen.

In diesem Beirag geht es um ein weiteres Steuerelement, die ComboB (Kombinationsfeld).

Die ComboBox kann sowohl auf einem Arbeitsblatt als auch in einer UserForm platziert werden.

Die UserForm wird Gegenstand eines späteren Beitrages sein, deshalb beschränke ich mich hier auf das Arbeitsblatt.

1. Was ist eine ComboBox und wie fügst Du sie ein?

Die ComboBox stellt eine Kombination aus TextBox und ListBox dar. Im Ergebnis ist sie eine Drop-Down-Liste, aus der der Nutzer hinterlegte Elemente auswählen und weiter verarbeiten kann.

Um eine ComboBox auf dem Arbeitsblatt anzulegen und sie in einem Makro verwenden zu können, brauchst Du eine Excel-Arbeitsmappe mit der Extension „xlsm“.

Gehe ins Menü Entwicklertools und klicke unter Steuerelemente auf Einfügen.
Wähle dort unter den ActiveX-Steuerelementen das Kombinationsfeld aus.

Dein Cursor verwandelt sich jetzt in ein kleines schwarzes Kreuz. Suche Dir einen Platz auf dem Arbeitsblatt, auf dem die ComboBox liegen soll. Führe das Kreuz dorthin und klicke mit der linken Taste.

Verschiebe sie ggf. an die richtige Position (im Entwurfsmodus unter den Entwicklertools).

Die ComboBox liegt jetzt über der Zelle B3, ist aber nicht Zellinhalt.

2. Die ComboBox formatieren

Das Steuerelement gefällt Dir so noch nicht? Kein Problem, Du kannst es formatieren.

Gehe im Menü Entwicklertools auf den Entwurfsmodus und klicke die ComboBox an. Wähle dann Eigenschaften aus. Du kannst hier z.B. diese Einstellungen vornehmen:

• BackColor bezeichnet die Hintergrundfarbe
• BorderColor bezeichnet die Rahmenfarbe.
• BorderStyle bezeichnet die Rahmenart.
• Height bezeichnet die Höhe, Width die Breite des Elementes
• Mit Font gibst Du die Schriftart, Schriftschnitt und Schriftgrad vor.
• Und mit ForeColor wählst Du die Schriftfarbe aus

3. Die ComboBox aus einer Liste füllen

Zum Füllen der ComboBox legst Du Dir am besten eine namentliche Liste an, z.B. diese:

Vergebe im Menü Formeln/Namen definieren für den Bereich B8:B13 den Namen „Namen“.

Gehe nun wieder in den Entwurfsmodus, wähle Eigenschaften und trage unter ListFillRange „Namen“ ein.

Verlasse den Entwurfsmodus und klicke den kleinen Pfeil im Steuerelement zur Auswahl an.

Das Menü öffnet sich, wähle z.B. „Hedwig“.

Soll das ausgewählte Element in einer Zelle des Arbeitsblattes ausgegeben werden, z.B. in E3, trage unter LinkedCell in den Eigenschaften „E3“ ein. Bis hierher hast Du noch ohne Makroprogrammierung arbeiten können.

4. Eine ComboBox mit VBA einfügen

Wie eine ComboBox manuell in ein Arbeitsblatt eingefügt wird, hast Du im Abschnitt 1 erfahren.

Es besteht ebenso die Möglichkeit, ein Steuerelement mit einem Makro einzufügen.

Die ComboBox soll im Arbeitsblatt „Tabelle2“ über der Zelle B3 angeordnet werden.
Verwende nun diesen Code:

Sub ComboBox1()
Worksheets(„Tabelle2“).Activate
ActiveSheet.OLEObjects.Add(ClassType:=“Forms.ComboBox.1″, Link:=False, _
DisplayAsIcon:=False, Left:=60, Top:=30, Width:=100, Height:=18) _
.Select
End Sub

Die ComboBox mit dem Namen „ComboBox1″wurde eingerichtet.

Mit Left wird der Abstand zum linken Rand, mit Top der Abstand zum oberen Rand festgelegt.

Die Standardbreite von 72 wurde auf 100 verändert.

Beachte, dass die Zelle B3 nicht direkt angesprochen wird. Die Lage des Steuerelementes auf dem Arbeitsblatt bestimmst Du mit den Left- und Top-Einstellungen.

Auf weitere Formatierungen wie Hintergrundfarbe, Rahmen, Schriftart und -größe, Schriftfarbe u.a. wurde verzichtet. Diese Einstellungen können auch im Nachhinein manuell vorgenommen werden.

5. Eine ComboBox mit VBA füllen

Du willst die ComboBox mit Auswahlelementen füllen. Schreibe dazu diesen Code in ein Modul:

Sub ComboBox1_Füllen()
With Worksheets(„Tabelle2“).ComboBox1
.AddItem „Horst“
.AddItem „Walter“
.AddItem „Hedwig“
.AddItem „Heinz“
.AddItem „Hilde“
.AddItem „Agnes“
End With
End Sub

Jetzt steht Dir die ComboBox mit Auswahlelementen zur Verfügung.

Eine weitere Möglichkeit, die ComboBox zu füllen, besteht darin, das Füllen der Box an das Öffnen der Arbeitsmappe zu binden.

Du hast im Arbeitsblatt „Tabelle2“ eine ComboBox hinterlegt.
Gehe in den Entwurfsmodus, klicke das Steuerelement an und wähle nun „Code anzeigen“. Wähle im Explorer „Diese Arbeitsmappe“.

Wähle nun im Bearbeitungsbereich aus der linken Drop-Down-Liste „Workbook“, aus der rechten „Open“. Es erscheinen Anfang und Ende des Codes.
Anfang   Private Sub Workbook_Open()
Ende       End Sub

Dazwischen kannst Du Deine Anweisungen schreiben, die das Makro ausführt sobald die Arbeitsmappe geöffnet wird.

Du willst die ComboBox mit Auswahlelementen füllen. Vervollständige dazu den Code so:

Private Sub Workbook_Open()
With Worksheets(„Tabelle2“).ComboBox1
.AddItem „Horst“
.AddItem „Walter“
.AddItem „Hedwig“
.AddItem „Heinz“
.AddItem „Hilde“
.AddItem „Agnes“
End With
End Sub

Der Code wird nicht in einem Modul abgelegt, sondern als Private Sub auf dem Steuerelement „ComboBox1“. Schließe die Mappe und öffne sie erneut.
Nun stehen die eingestellten Elemente zur Auswahl zur Verfügung.

6. Die Anwendung einer Combobox

Angenommen, Du hast in Tabelle2 eine Liste hinterlegt, die die Namen Deiner Mitarbeiter und die geleisteten Stunden ausweist.

Mit einem Makro soll nun bei einem angenommenen Stundensatz von 18 € der zu zahlende Lohn berechnet und in Tabelle5 ausgegeben werden.

Zusätzlich willst Du in E4 das aktuelle Datum eintragen lassen.
Schreibe z.B. diesen Code in ein Modul, um die Berechnung auszuführen:

Sub Combobox1_Rechnen()
Dim strSuch As String
Dim rngFund
Dim intLohn As Integer
Dim strStunden As String
Dim rngLeute As Range
Set rngLeute = Worksheets(„Tabelle2“).Range(„B8:B13“)
strSuch = Worksheets(„Tabelle5“).ComboBox1.Value
Set rngFund = rngLeute.Find(what:=strSuch, LookIn:=xlValues)
intLohn = rngFund.Offset(0, 1) * 18
Worksheets(„Tabelle5“).Range(„D7“) = intLohn
Range(„E2“).Activate
ActiveCell.Formula = „=TODAY()“
End Sub

Füge noch eine Schaltfläche auf Arbeitsblatt Tabelle5 ein, beschrifte diese mit „Berechnen“ und weise ihr das Makro „Combobox1_Rechnen“ zu.

Nach Betätigen der Schaltfläche wird das Makro ausgeführt und die Ergebnisse ausgegeben.

Wähle nun mit der ComboBox einen anderen Mitarbeiter aus und klicke die Schaltfläche „Berechnen“.

Du erhältst nun den Lohn für den neu gewählten Mitarbeiter.

7. Von einander abhängige Comboboxen

Angenommen, ein Unternehmen hat Filialen in drei Bundesländer in unterschiedlicher Anzahl.

Lege Dir z.B. diese Listen an und vergib die Namen „Land“, „Hessen“, „Sachsen“ und „Niedersachsen“.

Die Landeslisten enthalten die Filialen.

Füge z.B. im Arbeitsblatt „Tabelle6“ zwei ComboBoxen ein.
Trage im Entwurfsmodus in den Eigenschaften von ComboBox1 unter ListFillRange „Land“ ein.

Öffne für ComboBox1 „Code anzeigen“ und füge als Private Sub diese Codezeilen ein.

Private Sub ComboBox1_Change()
Worksheets(„Tabelle6“).ComboBox2.ListFillRange=ComboBox1.Value
End Sub

Damit regelst Du, dass nach Auswahl eines Landes in ComboBox1 das Land in ComboBox2 der Eintrag für ListFillRange wird.

Wählst Du in ComboBox1 z.B. „Niedersachsen“ aus, steht Dir in ComboBox2 die Liste „Niedersachsen“ zur Verfügung und Du kannst z.B. „Cuxhaven“ wählen.

Du willst nun den Umsatz der ausgesuchten Filiale abfragen.
Dazu liegt Dir eine Auswertung vor, die im Blatt „Tabelle7“ der Arbeitsmappe liegt.

Hieraus willst Du mit VBA den Umsatz z.B. für Cuxhaven abfragen.
Ergänze das Arbeitsblatt um eine Zeile für den Umsatz und schreibe in ein Modul diesen Code:

Sub Umsatz_Abfragen()
Dim strSuch As String
Dim rngFund
Dim intUmsatz As Integer
Dim strOrt As String
Dim rngFilialen As Range
Set rngFilialen = Worksheets(„Tabelle7“).Range(„B3:B14“)
strSuch = Worksheets(„Tabelle6“).ComboBox2.Value
Set rngFund = rngFilialen.Find(what:=strSuch, LookIn:=xlValues)
intUmsatz = rngFund.Offset(0, 1)
Worksheets(„Tabelle6“).Range(„D9“) = intUmsatz
End Sub

Lege über Zelle C9 eine Schaltfläche, beschrifte sie mit „Berechnen“ und weise ihr das
Makro „Sub Umsatz_Abfragen“ zu.

Klicke auf die Schaltfläche. In Zelle D9 ist nun der Umsatz der Filiale Cuxhaven ablesbar.

Wähle ein anderes Land und einen zugehörigen Ort und klicke wiederum die Schaltfläche. In D9 wird nun der zugehörige Umsatz ausgegeben.