Office: Dropdown Menue zum Sheets wechseln

Hallo, als Excel Anfäger versuche ich mich jetzt seit 5 Stunden an etwas vermeindlich sehr einfachem.

Umschrieben:

A) Ich brauche in den Feldern A1, B1 auf jedem Tabellensheet ein Dropdown Menü.
B) Dieses Menü soll - wenn man es öffnet - alle vorhandenen Tabellensheets anzeigen.
C) Wenn man eines dieser Tabellensheets in dem DropDown Menü auswählt, landet man auf dem entsprechenden Sheet.

Derzeit sitze ich auf dem Schlauch, ich habe ein Beispiel gefunden, wie man die von mir gewünschte Funktion als Add-In gestalten kann, dies ist aber nicht Sinn der Sache.

A bekommt man ja hin... könnte mir jemand dabei helfen?

Danke

 

Es wird auf alle Fälle ein Haufen VBA, denn...

Eine ComboBox braucht einen "ListFillRange", aus dem es die Werte liest, die in der Ausklappliste erscheinen soll. Dieser Bereich muß dynamisch angepaßt werden, wenn ein neues Blatt dazukommt, ein bestehendes gelöscht wird oder sich der Name eines Blattes ändert. Bei "neues Blatt" müßte außerdem noch die ComboBox in dem neuen Blatt erzeugt werden.

Das würde sich natürlich vereinfachen lassen, wenn der Aufbau der Mappe garantiert statisch ist. (Alternativ kann das auch manuell gepflegt werden, wenn es nicht zu häufig vorkommt.)

Dann muß beim Auswählen eines Wertes aus der Liste per VBA der Blattwechsel durchgeführt werden.

Und manuell ist schonmal der vorbereitende Aufwand, auf jedem Blatt die ComboBox einzubauen und mit den entsprechenden Parametern für ListFillRange und Ereignis zu füllen.

Das Ereignis ruft dann eine zentrale Routine auf, die den Blattwechsel durchführt. Zentral, weil sie für alle ComboBoxen gleich ist. Das Ereignis der jeweiligen ComboBox besteht also eigentlich nur aus einer Zeile.

Und den ganzen Aufwand nur, um das zu ermöglichen, was durch Klick auf die Karteireiter am unteren Rand der Blätter eh schon möglich ist?

Gruß, Michael

PS: Ich würde jetzt gerne ein Beispiel anhängen, aber das Forum meint, ich hätte genug Attachments insgesamt und ich weiß jetzt noch nicht, wo ich mich von alten trennen kann.

 

*nickt* ja, eigentlich das alles, nur um die Reiter unten zu umgehen. Die Dateien haben teilweise 30 Sheets + 4-5 Sheets die man nicht "zum anschauen" sind (das ist aber ein anderes Problem...).

Da ist eine "komfortablere" Navigation, Dropdown, angelehnt an "Favoriten" bei einem Browser eine sehr angenehme Sache. So denke ich es mir zumindest.

Aufgrund der Größe und der häufigen Wechsel ist eine statische Variante leider nicht möglich.

Ich habe erste Schritte Richtung erfolg gefunden. Es gibt ein Add-In, welches genau diesen Job übernimmt. Jetzt muß ich nur noch das Add-In in eine Combobox innerhalb des Dokuments umwandeln. Daran hänge ich,..


Das Add-In hat 3 Funktionen

Public tbrTabellen As CommandBar
Public cbxTabellen As CommandBarControl

Sub Toolbar()
On Error GoTo Fehler
Set tbrTabellen = CommandBars.Add(Name:="Tabellen", Position:=msoBarFloating, Temporary:=True)
With tbrTabellen
Set cbxTabellen = .Controls.Add(Type:=msoControlDropdown, Before:=.Controls.Count + 1)
cbxTabellen.Width = 150
cbxTabellen.OnAction = "SelectSheet"
End With
Fehler:
CommandBars("Tabellen").Visible = True
End Sub

Sub ReadSheets()
Dim tmpSheet
Dim tmpSel
cbxTabellen.Clear
For tmpSheet = 1 To Sheets.Count
cbxTabellen.AddItem Sheets(tmpSheet).Name
If Sheets(tmpSheet).Name = ActiveSheet.Name Then tmpSel = tmpSheet
Next
cbxTabellen.ListIndex = tmpSel
End Sub

Sub SelectSheet()
-> hier fehlt leider der Text

Bin weiter am basteln und testen und Haare raufen

 

Sub SelectSheet()
Sheets(cbxTabellen.List(cbxTabellen.ListIndex)).Select
End Sub

 

Ok, ich hab mir eben mal das Ding angesehen... Es ist keine ComboBox auf den jeweiligen Blättern, würde aber grundsätzlich auch seinen Zweck erfüllen, denke ich. Der Trick ist simpel: Man nimmt eben eine Toolbar, die nicht angedockt ist, sondern über der Mappe, z.B. auf den Zellen A/B1, schwebt.

War Dir jetzt nicht klar, wie man das Ding aktiviert? Das scheint mir simpel:

a) Erzeuge ein neues Modul (Modul1) und füge alles vom 1. "Public" bis zum letzten "End Sub" dort ein.

b) gehe dann in "DieseArbeitsmappe" und wähle dort in der Ausklappliste oben "Workbook" aus. Es sollte sich ein Prozedur-Rumpf für das "Open"-Ereignis gebildet haben. Dort nur noch:

Code:

Toolbar
ReadSheets

zwischen die beiden Zeilen einfügen.

Dann funktioniert das ganze prinzipiell. Speichern, schließen, neu öffnen... et voila! Die Toolbar mit der Ausklappliste der Tabellenblätter ist da.

Finetuning:
- Wenn ein Tabellenblatt gelöscht oder erstellt wird, müßte "ReadSheets" aufgerufen werden.
- Die Toolbar gleich "links oben" positionieren wäre schöner.
- Die Toolbar vor "Schließen" zu schützen wäre gut.
- Die Blätter sollten in der Liste vielleicht in einer Form (alphabetisch) sortiert werden.

Gruß, Michael

 

Danke erstmal für Deine Zeit.

Die "Add-In" Variante habe ich zum laufen gebracht. Der Haken ist nur, dass es so einfach wie möglich zu handhaben sein muß. Häufiger Wechsel der festen Menüpunkte ist da nicht machbar.

Dh. Die Variante eines "floating Menuepunkt" über den Zellen A1, B1 würde gehen.

Lieber wäre mir jedoch weiterhin, wenn man dies über eine fest - auf jedem Tabellensheet - eingebettete DropDown Box machen könnte (schon aus ehrgeiz Gründen).

Ich versuche mich derzeit die 3 Addin Funktionen komplett zu verstehen und in Funktionen für eine Combobox umzuwandeln. Vom "Prinzip" her, erfüllt das Add-In absolut seinen Zweck. Es zeigt eine Box und die zeigt alle Sheets an.

 

Soweit ist mein derzeitiger Stand:

1) Ich habe eine DropDown Box (Formular) Namens "Dropdown1"

Diese Funktion soll die DD Box "füllen".

Sub ReadSheets()

Dim tmpSheet
Dim tmpSel
Dropdown1.Clear
For tmpSheet = 1 To Sheets.Count
Dropdown1.AddItem Sheets(tmpSheet).Name
If Sheets(tmpSheet).Name = ActiveSheet.Name Then tmpSel = tmpSheet
Next
Dropdown1.ListIndex = tmpSel

End Sub

Das funkioniert aber nicht, Grund: ich spreche die Dropdownbox falsch an. Erste Fehlermeldung in der Zeile
Dropdown1.Clear

2) Wenn ich in der Dropdownbox etwas auswähle, soll er den Sheet mit dem ausgewählten Namen öffnen

Sub SelectSheet()
Sheets(Dropdown1.List(Dropdown1.ListIndex)).Select
End Sub

Funktioniert aber auch nicht

und Final wird noch folgendes für das ganze Workbook ausgeführt

3)
Private Sub Workbook_NewSheet(ByVal Sh As Object)
ReadSheets
End Sub

Private Sub Workbook_Open()
ReadSheets
End Sub

Das funktioniert schonmal ;)

 

Du wirst wahrscheinlich die ComboBox aus der "Steuerelement-Toolbox" brauchen. Ich benutz die auf alle Fälle nur. Die "Formular"-Dinger lassen sich lange nicht so variabel benutzen.

Das wäre dann das, was ich zum Thema "ListFillRange" schrieb... ;-)

Nö, glaube ich... ;-) Das ist nämlich nur eine Prozedur, die in der Gegend herumsteht, aber von niemandem aufgerufen wird. D.h.: Du brauchst ein Ereignis, in dem dann die Prozedur SelectSheet aufgerufen wird. Dafür würde sich dann das "Change"-Ereignis der ComboBox anbieten.

Na siehste, immerhin doch schonmal ein Schritt in die richtige Richtung.

Es fehlt aber noch ein wichtiger Schritt, der nicht ganz unhaarig ist:

Wenn ein neues Blatt erzeugt wird, reicht es nicht, nur einfach die Liste der Blätter zu aktualisieren. Du muß dann auch eine ComboBox erzeugen, die sich genauso verhält, wie die auf den anderen Blättern. Das bedeutet vor allem auch: Bindung an das Change-Ereignis.

Und um diese Klippe zu umgehen ist der Trick mit der "floating toolbar" eigentlich schon ganz schön pfiffig... ;-)

Gruß, Michael

 

Kleine Schritte Richtung erfolg:

1)

Sub Sheet_wechsel()
Sheets(ComboBox1.List(ComboBox1.ListIndex)).Select
End Sub

2)
Sub Combobox_laden()
Dim tmpSheet
Dim tmpSel
ComboBox1.Clear
For tmpSheet = 1 To Sheets.Count
ComboBox1.AddItem Sheets(tmpSheet).Name
If Sheets(tmpSheet).Name = ActiveSheet.Name Then tmpSel = tmpSheet
Next
ComboBox1.ListIndex = tmpSel
End Sub
3)
Private Sub ComboBox1_Change()
Sheet_wechsel
End Sub

So, wenn ich die 3 Funktionen nun DIREKT in jedes Tabellensheet kopiere und jeweils die COmboboxen darin umbenenne geht es. Wenn ich die ersten 2 Funktionen in "Module" stecke gibt es immer eine Fehlermeldung, er kennt Combobox1 nicht.

Jetzt die Frage aller Fragen,

wie bekomme ich es hin (wohl nur arrangement), dass die Funktionen in dem Modul stehen und für jedes Arbeitsblatt gelten und für jede Combobox1.

Ziel ist es, dass ich bei jedem Sheet per Hand eine Combobox einfüge.

Diese wird in jedem Sheet "Combobox1" heißen und in dem Blatt wird die Funktion Private Sub ComboBox1_Change() hinterlegt.

Dann sollte alles funktionieren... Fraglich ist auch, wie ich die Funktion Combobox_laden so umschreiben kann, dass sie alle Comboboxen auf jedem Tabellensheet füllt (heißen ja alle Combobox1).

Für weitere Tips bin ich immer offen. Habe die Excel (mit ihrem Macken) mal angehängt.

ps doch nicht angehängt, maximale Größe wurde bereits erreicht?

 

Yup... Das Modul ist dann ja nicht an ein Blatt gebunden und kann von daher nicht unterscheiden, welches "ComboBox1" (also von welchem Blatt) Du denn nun meinst. Einzig sinnvolle Möglichkeit: Die Referenz auf die ComboBox oder dessen Wert per Parameter an die Prozedur(en) im Modul übergeben.

Also sowas wie:

Code:

Private Sub ComboBox1_Change()
    dim sht as string
    sht=ComboBox1.List(ComboBox1.ListIndex))
    Sheet_wechsel sht
End Sub

Sub Sheet_wechsel(sht as string)
    Sheets(sht).Select
End Sub

oder

Code:

Private Sub ComboBox1_Change()
    dim cbo as object
    cbo=ComboBox1
    Sheet_wechsel cbo
End Sub

Sub Sheet_wechsel(cbo as object)
    Sheets(cbo.List(cbo.ListIndex)).Select
End Sub

(Ist jetzt ungetestet, hoffe ich hab mich da nicht verhauen.)

Nach wie vor das Problem: Was mußt Du alles machen, wenn ein neues Blatt dazukommt?

In einer Schleife (worksheets(1)..worksheets(worksheets.count) alle Blätter durchlaufen, und dort jeweils die ComboBox1 ansprechen.

Ja, habe das gleiche Problem. Scheint irgendwas grundsätzliches am Forum zu haken.

Gruß, Michael