VBA-Lektion 12.3. Steuerelemente (Controls)

Controls (Controls) können auch außerhalb von Formularen verwendet werden. Im folgenden Beispiel verwenden wir die Steuerelemente direkt auf dem Arbeitsblatt.

Beachten Sie, dass der „Designer-Modus“ („Design mode“) aktiviert sein muss, um ein bestimmtes Steuerelement auf dem Arbeitsblatt zu ändern (und auch deaktiviert sein muss, um dieses Steuerelement zu verwenden).

Bildlaufleiste (ScrollBar)

Bevor wir uns das nächste Beispiel ansehen, schauen wir uns Folgendes an:

Nun möchten wir den Zellen eine Hintergrundfarbe hinzufügen und sie anhand der Position des Schiebereglers in einem definierten Bereich von 30 Zeilen mal 10 Spalten auswählen.

Die Eigenschaften des vertikalen Schiebereglers zum Scrollen des Bildschirms sind wie folgt:

Der horizontale Schieberegler ist derselbe, außer Max: 10.

Der folgende Code wird immer dann ausgeführt, wenn sich der Wert des vertikalen Schiebereglers (Value) ändert:

 'Grauer Hintergrund in Zellen
 Cells.Interior.Color = RGB(240, 240, 240)

 'Wir wenden Farbe an und wählen eine Zelle aus
 With Cells(ScrollBar_vertical.Value, ActiveCell.Column) 'Definieren Sie die Zelle mit Value (Value)
     .Interior.Color = RGB(255, 220, 100) 'Wir verwenden orange Farbe
     .Select 'Wir wählen eine Zelle aus
 End With

Dieser Code wird ausgelöst, wenn die Ereignisse Change und Scroll auftreten, und führt Anweisungen aus, unabhängig davon, auf welchen Teil des Schiebereglers wir geklickt haben.

Das Folgende ist der Code für den vertikalen Schieberegler:

Private Sub vertical_bar()
     'Wenden Sie einen grauen Hintergrund für die Zellen an
     Cells.Interior.Color = RGB(240, 240, 240)
     
     'Wenden Sie den Hintergrund an und wählen Sie die Zelle aus
     With Cells(ScrollBar_vertical.Value, ActiveCell.Column)
         .Interior.Color = RGB(255, 220, 100) 'Orange
         .Select 'Wir wählen eine Zelle aus
     End With
End Sub

Private Sub ScrollBar_vertical_Change()
     vertical_bar
End Sub

Private Sub ScrollBar_vertical_Scroll()
     vertical_bar
End Sub

Und hier wiederum der Code für den horizontalen Schieberegler:

Private Sub horizontal_bar()
     'Wenden Sie einen grauen Hintergrund für die Zellen an
     Cells.Interior.Color = RGB(240, 240, 240)

     'Wenden Sie den Hintergrund an und wählen Sie die Zelle aus
     With Cells(ActiveCell.Row, ScrollBar_horizontal.Value)
         .Interior.Color = RGB(255, 220, 100) 'Orange
         .Select 'Wir wählen eine Zelle aus
     End With
End Sub

Private Sub ScrollBar_horizontal_Change()
     horizontal_bar
End Sub

Private Sub ScrollBar_horizontal_Scroll()
     horizontal_bar
End Sub

Dropdown und ListBox (ComboBox und ListBox)

Dies ist der Ausgangspunkt für unser nächstes Beispiel:

Sie können die Beispieldatei Excel herunterladen: userform4.xls

Beim Ausführen des Formulars möchten wir, dass die 4 Länder in die Dropdown-Liste geladen werden (mithilfe der Methode AddItem):

Private Sub UserForm_Initialize()
     For i = 1 To 4 '=> um 4 Länder zu füllen
        ComboBox_Country.AddItem Cells(1, i) 'Wir addieren die Werte der Zellen von A1 bis A4 mithilfe einer Schleife
    Next
End Sub

Wenn sich der Wert des Dropdown-Menüs ändert, möchten wir die Städte des ausgewählten Landes mithilfe einer Schleife hinzufügen, die der vorherigen ähnelt.

Dazu müssen wir die Nummer der Spalte sowie die Anzahl der Städte (Zeilen) in dieser Spalte kennen.

Die Eigenschaft ListIndex enthält die Sequenznummer des ausgewählten Elements in der Dropdown-Liste (im Gegensatz zu Value, das den Wert des Listenelements enthält). Beachten Sie, dass ListIndex mit der Zahl 0 beginnt.

Die Spaltennummer wird ermittelt durch:

column_number = ComboBox_Country.ListIndex + 1

Um die Anzahl der Zeilen in der ausgewählten Spalte für ein bestimmtes Land zu ermitteln, können wir die Nummer der letzten nicht leeren Zelle ermitteln:

rows_number = Cells(1, column_number).End(xlDown).Row

Anhand dieser Informationen ist es nun möglich, eine Schleife zu erstellen, um die Städte zur Liste hinzuzufügen:

Private Sub ComboBox_Country_Change()
     'Liste löschen (andernfalls werden Städte sofort hinzugefügt)
     ListBox_Cities.Clear
     
     Dim column_number As Integer, rows_number As Integer
     
     'Die Sequenznummer des ausgewählten Elements (ListIndex beginnt mit 0):
     column_number = ComboBox_Country.ListIndex + 1
     'Anzahl der Zeilen in der ausgewählten Spalte mit Ländern:
     rows_number = Cells(1, column_number).End(xlDown).Row

     For i = 2 To rows_number '=> Liste mit Städten füllen
        ListBox_Cities.AddItem Cells(i, column_number)
     Next
End Sub

Hinweis: Wir könnten den Code kürzen, aber das würde die Lesbarkeit beeinträchtigen:

Private Sub ComboBox_Country_Change()
     ListBox_Cities.Clear
     For i = 2 To Cells(1, ComboBox_Country.ListIndex + 1).End(xlDown).Row
         ListBox_Cities.AddItem Cells(i, ComboBox_Country.ListIndex + 1)
     Next
End Sub

Die von uns ausgewählte Stadt wird in das Textfeld „Choice“ („Choice“) eingetragen:

Private Sub ListBox_Cities_Click()
     TextBox_Choice.Value = ListBox_Cities.Value
End Sub

Ein fertiges Beispiel können Sie in dieser Excel-Datei sehen: userform4b.xls