Alle Tabellen einer Arbeitsmappe in ListBox auflisten

Manchmal ist es ganz hilfreich, wenn man alle Tabellen einer Arbeitsmappe in einer Listbox in einem UserForm anzeigen lässt.

Das erste Makro befüllt die ListBox mit den Namen aller Tabellen einer Arbeitsmappe.
Da es bei der Bearbeitung dazu kommen kann, dass Tabellenblätter hinzugefügt oder gelöscht werden, wird die Liste bei jedem Aufruf neu eingelesen.

Tabellen in ListBox auflisten
Tabellen in ListBox auflisten


Sub UserForm1Anzeigen()
'
'Ruft UserForm1 auf
'
call navigation_loeschen
Call ListBox2_Initialize

UserForm1.Show
End Sub

Private Sub ListBox2_Initialize()
'
' Befüllt die ListBox2 mit allen in der Arbeitsmappe vorhandenen Tabellen
'

Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
UserForm1.ListBox2.AddItem sht.Name
Next sht
End Sub


Private Sub navigation_loeschen()
'
' Löscht die Einträge im Navigationsfeld
'

UserForm1.ListBox2.Clear
End Sub

Navigation via ListBox in einer Excel Arbeitsmappe

Navigation für Excel Arbeitsmappe

Mit Hilfe eines Listenfelds (ListBox) kann man sehr einfach eine Navigation innerhalb einer Excel-Arbeitsmappe erstellen.

Die Navigation soll folgende Funktionen beinhalten

  • Bei jedem Öffnen der Arbeitsmappe sol sich die Navigation dynamisch füllen, je nach Anzahl und Benennung der einzelnen Tabs innerhalb der Arbeitsmappe
  • Ein Klick auf einen Eintrag setzt den Cursor in das ausgewählte Tabellenblatt.

ListBox in Tabelle einfügen

In unserem Beispiel soll die Navigation in die Tabelle 1 eingefügt werden.

ListBox einfügen
ListBox einfügen

Neben der ListBox könnt ihr gleich noch Buttons erstellen, die für die folgenden Aufgaben zuständig sind

  • Befüllen der Navigations, falls der User dies manuell vornehmen möchte
  • Löschen der Navigation
Navigation innerhalb einer Excel Arbeitsmappe
Navigation innerhalb einer Excel Arbeitsmappe

Der Code

Nachdem das Layout der Navigation in der Tabelle 1 erstellt wworden ist, gilt es den Code einzufügen.
Alle Elemente haben wir in die Tabelle 1 eingefügt, weshalb wir einen Teil des Codes auch dort reinschreiben werden.

Code für Navigationsfeld
Code für Navigationsfeld

Option Explicit

' Ruft das Makro UserForm_Initialize() auf
Sub navigation()
Call UserForm_Initialize
End Sub

' Befüllt die ListBox
Private Sub UserForm_Initialize()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
ListBox1.AddItem sht.Name
Next sht
End Sub

' Setzt die Einträge in der ListBox als Hyperlink
Private Sub ListBox1_Click()
Sheets(ListBox1.ListIndex + 1).Activate
End Sub

' Löscht die Einträge im Navigationsfeld
Sub navigation_loeschen()
Sheets("Tabelle1").ListBox1.Clear
End Sub

Die Makros UserForm_Initialize und ListBox1_Click befüllen die ListBox und aktivieren die Verlinkung zu den Tabellenblättern. Lässt man das Makro ListBox1_Click weg, wird der Cursor bei einem Klick auf einen Eintrag nicht in das entsprechende Tabellenblatt gesetzt.

Das Makro navigation ruft das Makro UserForm_Initialize auf, das Makro benötigen wird, damit der Button in Tabelle 1 das Navigationsfeld befüllen kann. Private-Makros können hierfür nicht verwendet werden.

Module

In den Modulen definieren wir, dass

  • Beim Öffnen der Arbeitsmappe der Navigationsbereich automatisch befüllt wird
  • Beim Schließen der Arbeismappe die Einträge im Navigationsbereich gelöscht werden

Für das Befüllen beim Öffnen ist das Modul mod_AutoStart zuständig.
Der Code lautet:

Option Explicit

Sub Auto_Open()
'
' Makros starten automatisch, wenn die Excelmappe geöffnet wird
'

Call UserForm_Initialize

End Sub

Für das automatische Löschen der Einträge wird der Code in das Modul mod_AutoClose geschrieben.

Option Explicit

' Makros werden automatisch ausgeführt, wenn die Arbeitsmappe geschlossen wird
Sub auto_close()

Call navigation_loeschen

 End Sub

Module erstellen
Module erstellen

Das Ergebnis

Das Ergebnis sollte so ähnlich aussehen wie hier:

Navigation für Excel Arbeitsmappe
Navigation für Excel Arbeitsmappe

Die erstellten Buttons in Tabelle 1 verfügen noch über keine Funktion. Dafür müssen die Makros noch zugewiesen werden.

Buttons - Makro zuweisen
Buttons – Makro zuweisen