Excel Dropdown Problem

Sascha_fecfe2 · 9. November 2019 um 06:31

Hallo,

ich möchte gerne ein dropdownmenu erstellen in dem ich auswählen kann auf welches register ich zugreifen will.

habe z.b. 3 register: tabelle 1 , tabelle 2 , tabelle 3

nun möchte ich in jedem register ein dropdown machen, damit ich (wie ein menu) auswählen kann auf welches blatt ich zugreifen möchte.

habe schlussendlich ca 20 register und möchte diese dann verstecken. und damit ich vom einen zum anderen wechseln kann brauche ich ein dropdown menu

hat jemand eine idee?

Franz_e6478d · 9. November 2019 um 06:32

Hallo Sascha,

unter Verwendung der Kombinationsboxen aus der Steuerelemente-Toolbox (EXCEL97) kann man mit zwei der Kombibox zugeordneten Makros jeweils die Auswahlliste erzeugen und nach Auswahl das gewählte Blatt sichtbar machen und das momentan sichtbare Blatt ausblenden, so ist immer nur eine Tabelle sichtbar. Den Code muss du in jedem Blatt, jeder Box zuordnen.

Private Sub ComboBox1\_Change()
 Registername = ActiveSheet.Name
 ActiveWorkbook.Sheets(ComboBox1.Value).Visible = True
 ActiveWorkbook.Sheets(ComboBox1.Value).Select
 ActiveWorkbook.Sheets(Registername).Visible = False
End Sub

Private Sub ComboBox1\_GotFocus()
' Aktualisiert beim Anklicken der Box die Auswahlliste
 Dim Blaetter()
 ReDim Blaetter(1 To ActiveWorkbook.Sheets.Count - 1)
 I = 1
 For Each Blatt In ActiveWorkbook.Sheets
 If Blatt.Name ActiveSheet.Name Then
 Blaetter(I) = Blatt.Name
 I = I + 1
 End If
 Next
 With ComboBox1
 .ColumnCount = 1
 .List() = Blaetter
 End With
End Sub

Gruß
Franz

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Reinhard_fb8cb7 · 9. November 2019 um 06:32

Private Sub ComboBox1\_Change()
 Registername = ActiveSheet.Name
 ActiveWorkbook.Sheets(ComboBox1.Value).Visible = True
 ActiveWorkbook.Sheets(ComboBox1.Value).Select
 ActiveWorkbook.Sheets(Registername).Visible = False
End Sub

Private Sub ComboBox1\_GotFocus()
' Aktualisiert beim Anklicken der Box die Auswahlliste
 Dim Blaetter()
 ReDim Blaetter(1 To ActiveWorkbook.Sheets.Count - 1)
 I = 1
 For Each Blatt In ActiveWorkbook.Sheets
 If Blatt.Name ActiveSheet.Name Then
 Blaetter(I) = Blatt.Name
 I = I + 1
 End If
 Next
 With ComboBox1
 .ColumnCount = 1
 .List() = Blaetter
 End With
End Sub

Hallo Franz,
hast du das getestet?
mit (angenommen es gibt 7 Blätter)
ReDim Blaetter(1 To ActiveWorkbook.Sheets.Count - 1)
dimensionierst du Blätter von 0 bis 6
aber in der For each Blatt -Schleife verteilst du Indexe von 1 bis 7, das muss doch krachen *denk*
Vielleicht I=1 ersatzlos streichen.
Gruß
Reinhard

Reinhard_fb8cb7 · 9. November 2019 um 06:32

Mein Fehler
Hallo Franz,
hast Recht, dadurch dass das aktive Sheet nicht mitgezählt wird stimmt dein Code, sorry für meinen falschen Gedanken.
Gruß
Reinhard

Sascha_fecfe2 · 9. November 2019 um 06:32

hallo und danke für die antwort. nun habe ich ein problem mit dem testen, da ich probleme habe mit dem visual basic. ich kenne mich da nicht so aus. wie muss ich das ganze einbinden? kommt immer ein fehler dass ein objekt fehl. ich denke mal die combobox. ist es euch möglich mir eine test excel datei hochzuladen?

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Anonym · 9. November 2019 um 06:34

Hallo Sascha.

Ich könnte mir denken, das dies hier ist, was Du suchst. Ich versuche mal, das alles genau zu beschreiben, also quasi eine schrittweise Erklärung.

Los gehts:

Öffne Deine Excel-Datei.
Gehe in das Menü „Extras“, dann auf „Makro“, dann auf „Visual Basic Editor“.

Nun bist Du im Visual Basic Editor.

Gehe in das Menü „Einfügen“ und klicke dort auf „Modul“, woraufhin ein neues Fenster angezeigt wird, das Modulfenster.
Kopiere den folgenden Code in das Modulfenster:
'ProgrammCodeAnfang
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()
Sheets(cbxTabellen.List(cbxTabellen.ListIndex)).Select
End Sub
'ProgrammCodeEnde

Schließe dieses Modulfenster.
Siehe nach links oben. Dort befindet sich der sogenannte Projekt-Explorer, in dem der Eintrag „DieseArbeitsmappe“ enthalten sein sollte.
Doppelklicke auf den Eintrag „DieseArbeitsmappe“, damit sich das Fenster „DieseArbeitsmappe“ öffnet.
Kopiere den folgenden Code in des Fenster „DieseArbeitsmappe“:
'ProgrammCodeAnfang
Private Sub Workbook_NewSheet(ByVal Sh As Object)
ReadSheets
End Sub

Private Sub Workbook_Open()
Toolbar
ReadSheets
End Sub
'ProgrammCodeEnde

Gehe ins Menü „Debuggen“ und klicke dort auf „Kompilieren von VBAProject“.
Schließe den Visual Basic Editor und kehre zu Excel zurück.
Du bist jetzt wieder in Excel.
Gehe ins Menü „Extras“ und klicke dort auf „Optionen“.
Wähle im Dialog „Optionen“ den Reiter „Sicherheit“.
Klicke auf dem Reiter „Sicherheit“ auf den Button „Makrosicherheit“.
Wähle die Sicherheitsstufe „Mittel“ oder „Niedrig“, damit die Makros nicht pauschal geblockt werden.
Beende die Dialoge mit „Ok“.
Speichere die Datei.
Beende Excel.
Öffne die Excel-Datei erneut.
Jetzt sollte folgendes Ergebnis vorhanden sein: auf dem Tabellenblatt ist eine neue Toolbar mit einem DropDown, der alle Tabellenblätter enthält.

Franz_e6478d · 9. November 2019 um 06:34

Hallo Sascha,

ich habe Dir eine kleine Beispiel-Datei per e-mail geschickt.

Das 1. Makro, das auf Wertänderung der ComboBox reagiert, habe ich noch etwas optimiert. Der Wert der Box wird jeweils auf „“ gesetzt. Anderfalls kann die jeweils in der ComboBox angezeigte Tabelle nicht ausgewählt werden.

Private Sub ComboBox1\_Change()
 If ComboBox1.Value = "" Then Application.Range("A3").Select: Exit Sub
 Registername = ActiveSheet.Name
 ActiveWorkbook.Sheets(ComboBox1.Value).Visible = True
 ActiveWorkbook.Sheets(ComboBox1.Value).Select
 ComboBox1.Value = ""
 ActiveWorkbook.Sheets(Registername).Visible = False
End Sub
Private Sub ComboBox1\_GotFocus()
 Dim Blaetter()
 ReDim Blaetter(1 To ActiveWorkbook.Sheets.Count - 1)
 I = 1
 For Each Blatt In ActiveWorkbook.Sheets
 If Blatt.Name ActiveSheet.Name Then
 Blaetter(I) = Blatt.Name
 I = I + 1
 End If
 Next
 With ComboBox1
 .ColumnCount = 1
 .List() = Blaetter
 End With
End Sub

Gruß
Franz

Franz_e6478d · 9. November 2019 um 06:34

Hallo Carsten,

dass ist die bessere und elegantere Lösung, die ich als Alternative auch vorgeschlagen hätte.
Leider bin ich im Einfügen/Ausblenden neuer Symbolleisten via VBA nicht so firm, dass ich das gleich anbieten konnte.

Mit folgender Anpassung kann man das Ganze so steuern, dass am Ende immer nur das momentan selektierte Blatt angezeigt wird. Alle anderen Blätter sind ausgeblendet.

Sub SelectSheet()
 Register = ActiveSheet.Name
 Sheets(cbxTabellen.List(cbxTabellen.ListIndex)).Visible = True
 Sheets(cbxTabellen.List(cbxTabellen.ListIndex)).Select
 If cbxTabellen.List(cbxTabellen.ListIndex) Register Then
 Sheets(Register).Visible = False
 End If
End Sub

Gruß
Franz

Winfried_Bertling · 9. November 2019 um 06:34

!!!Viel einfacher!!!

Erstelle Dir ein neues Blatt und nimmst ein Schaltsymbol aus den Zeichnugstools, das Du dann unter „Verknüpfung erstellen mit …“ individuell einbinden kannst. Schick ist hiebei zusätzlich, das Du das Schaltsymbol auch noch selbst beschriften kannst.

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Anonym · 9. November 2019 um 06:34

Hallo Franz.

Ja, das mit den Toolbars ist mir anfänglich auch ziemlich schwer gefallen, insbesondere mit so einem DropDown. Da habe ich schon ganz schön lange dran getüftelt. Aber irgendwann hatte ich es dann herausgefunden; mit Makros aufzeichen und probieren, probieren, probieren.

MfG
C.Mehnert

Sascha_fecfe2 · 9. November 2019 um 06:34

Hallo Franz, habe deine Email leider nicht erhalten. Kannst Du mir sie nochmals mailen? [email protected]

Danke im Voraus

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Sascha_fecfe2 · 9. November 2019 um 06:34

SUPER!!!
Super…
Vielen Dank, das ist genau das wonach ich gesucht habe!

Jetz habe ich noch ein paar Fragen dazu:

-Ich habe ein Datenblatt mit Verweisen ausgeblendet, damit man nichts
-wichtiges ändern kann. Beim Dropdownmenu ist aber dieses Blatt
-wieder sichtbar. Ist es möglich dies wieder zu unterdrücken?

-Und ist es möglich dass man die Position festlegen kann, wo dieses
-Dropdown erscheint, beim öffnen der Datei?

Anonym · 9. November 2019 um 06:36

Hallo Sascha.
Vielen Dank für Deine Zustimmung. Freut mich, dass ich helfen konnte.

-Ich habe ein Datenblatt mit Verweisen ausgeblendet, damit man
nichts
-wichtiges ändern kann. Beim Dropdownmenu ist aber dieses
Blatt
-wieder sichtbar. Ist es möglich dies wieder zu unterdrücken?

Ja, das ist möglich. Gehe dafür wie folgt vor:

Gehe wieder über die Menüs „Extras“ > „Makro“ und Visual Basic Editor in den Visual Basic Editor.
Klicke im Projekt-Explorer (oben links) auf den Namen der Tabelle, die Deine ganzen Verweise enthält.
Drücke die Taste „F4“, damit sich das sogenannte "Eigenschaften-Fenster öffnet, sofern es noch nicht geöffnet ist.
Suche in diesem Eigenschaften-Fenster nach dem Eintrag „Visible“ und markiere ihn durch einfachen Klick.
Nachdem dieser Eintrag markiert wurde, taucht auf der rechten Seite des Eigenschaften-Fensters ein kleiner DropDown-Button auf, den Du bitte anklickst.
Wähle aus diesem DropDown den Eintrag „2 - xlSheetVeryHidden“
Doppelklicke im Projekt-Explorer auf den Eintrag „Modul1“. (möglicherweise musst Du dafür den Ordner „Module“ im Projekt-Explorer erst öffnen)
Wenn nun das Fenster „…xls - Modul1 (Code)“ geöffnet ist, musst Du die Prozedur "Sub ReadSheets() ersetzen durch den folgenden Code:

'ProgrammCodeAnfang
Sub ReadSheets()
Dim tmpSheet
Dim tmpSel
cbxTabellen.Clear
For tmpSheet = 1 To Sheets.Count
If Sheets(tmpSheet).Visible 2 Then 'Diese Zeile ist neu
cbxTabellen.AddItem Sheets(tmpSheet).Name
End If 'Diese Zeile ist Neu
If Sheets(tmpSheet).Name = ActiveSheet.Name Then tmpSel = tmpSheet
Next
cbxTabellen.ListIndex = tmpSel
End Sub
'ProgrammCodeEnde

So, das war’s eigentlich schon für das Tabellenblatt.

-Und ist es möglich dass man die Position festlegen kann, wo
dieses
-Dropdown erscheint, beim öffnen der Datei?

Ja, das ist auch möglich. Das funktioniert auf folgende Weise:

Gehe in die Prozedur „Toolbar“, die sich ja auch im Fenster von „Modul1“ befindet.

Jetzt kommt es darauf an, was Du meinst mit Position festlegen. Ich gehe mal davon aus, dass Du diese Toolbar nicht so auf dem Tabellenblatt „herumliegen“ haben möchtest, sondern sie oben bei den bereits vorhandenen plazieren möchtest. Wenn dem so ist, gehe wie folgt vor:
2. Gehe mit dem Cursor in die zweite Zeile dieser Prozedur und zwar dorthin, wo geschrieben steht: „Position:=msoBarFloating“.
3. Jetzt kannst Du den Text „msoBarFloating“ ersetzen durch z.B. „msoBarTop“. Dann wird die Toolbar neben oder unter den anderen bereits vorhandenen Toolbars angezeigt. Dann gibt es noch „msoBarBottom“, was die Toolbar dann nach ganz unten plaziert.

Da ich Deine Bildschirmauflösung nicht kenne, kann ich zur Positionierung der Toolbar nur allgemeine Angaben machen.
Wenn Du sie z.B. links neben der Format-Symbolleiste plazieren willst, dann musst Du sie auf „msoBarTop“ einstellen und noch folgenden Code in die Prozedur „Toolbar“ einfügen und zwar vor „CommandBars(„Tabellen“).Visible = True“:
'ProgrammCodeAnfang
CommandBars(„Tabellen“).RowIndex = CommandBars(„Formatting“).RowIndex
'ProgrammCodeEnde

MfG
C.Mehnert

Sascha_fecfe2 · 9. November 2019 um 06:36

Saubere Sache! Vielen lieben Dank. hat prima geklappt!

Danke für all eure hilfe!!

sascha