Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis in jedem Tabellenblatt
25.05.2017 14:12:40
Martin
Servus!
Wie kann man in jedem Tabellenblatt ein Inhaltsverzeichnis von allen Tabellenblättern erstellen? Die Reihenfolge in den Verzeichnissen soll gleich sein wie die Anordnung der Blätter.
Es Wäre schön, wenn die Inhaltsverzeichnisse sich automatisch anpassen würden, wenn man z.B. Namen der Blätter verändert oder es neue Blätter hinzukommen.
Nice to have aber kein muss: Das Tabellenblatt, was aktiv ist soll im Verzeichnis hervorgehoben werden. Wenn man mit der Maus über die Links im Verzeichnis fährt, dann die a) sollen die Links fetter werden b) soll ein kleines Vorschaufenster kommen
Ich freue mich auf eure Lösungen
Gruß
Martin

50
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit ner antiken Excel4-Makrofunktion
25.05.2017 14:53:01
Martin
Hallo WF,
bei deinem Link geht es um die Auflistung der Dateien eines Verzeichnisses.
Ich benötige dagegen ein Inhaltsverzeichnis bestehend aus den Tabellenblättern einer Arbeitsmappe.
Gruß
Martin
Stimmt - da hab ich mich vertan.
25.05.2017 15:05:04
WF
.
AW: hiermit könntest Du das fummeln ?
25.05.2017 20:55:36
Martin
Danke!
Ideen ?
25.05.2017 16:26:10
Martin
Hat sonst keiner eine Idee?
AW: Ideen ?
25.05.2017 16:31:15
Werner
Hallo Martin,
Code gehört ins Codemodul von "DieseArbeitsmappe"
-Makroeditor öffnen Alt F11 - Doppelklick auf "DieseArbeitsmappe" - Code rechts ins Codefenster kopieren
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim raBereich As Range
Dim raZelle As Range
Dim loLetzte As Long
Dim i As Long
Dim ws As Worksheet
i = 4
For Each ws In ThisWorkbook.Sheets
ActiveSheet.Cells(i, 1) = ws.Name
i = i + 1
Next ws
With ActiveSheet
loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
Set raBereich = .Range(.Cells(4, 1), .Cells(loLetzte, 1))
For Each raZelle In raBereich
If raZelle = .Name Then
raZelle.Font.Color = vbRed
Else
raZelle.Font.Color = vbBlack
End If
Next raZelle
End With
Set raBereich = Nothing
End Sub
Was du mit den Links meinst, da habe ich leider keine Ahnung. Vor allem - was für Links?
Gruß Werner
Anzeige
AW: Ideen ?
25.05.2017 17:12:34
Martin
Hallo Werner,
danke für die schnelle Unterstützung!
Mit Links meinte ich die Hyperlinks, da war ich nicht deutlich, sorry. Und "fett" sollen die Hyperlinks werden, über die man mit der Maus fährt.
Kannst du das ergänzen?
und vielleicht noch das:
a) In der Zelle A1 soll "Inhaltsverzeichnis" stehen.
b) Das Inhaltsverzeichnis selbst soll ab B3 beginnen. Daneben ab A3 soll die Nummerierung folgen.
also:

Inhaltsverzeichnis:
1. TB1
2. TB2
etc.

c) Die Breite der Spalte und der Nummerierung soll automatisch an der Länge der Texte angepasst werden
d) Der Bereich mit dem Inhaltsverzeichnis, also die Spalten A bis B sollen immer erscheinen, auch wenn im Datenbereich rechts Filter verwendet werden. Das ist wahrscheinlich zu viel verlangt oder?
Bin super gespannt auf deine Antwort :-)
Gruß
Martin
Anzeige
AW: Ideen ?
25.05.2017 18:16:30
Werner
Hallo Martin,
zunächst mal nur das Makro bzgl. der Erstellung des Inhaltsverzeichnisses. Für die Hyperlinks habe ich im Moment keine Zeit.
Bzgl. des Filters: Da wüsste ich nicht wie das geht, der Filter blendet ja komplette Zeilen aus.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim raBereich As Range
Dim raZelle As Range
Dim loLetzte As Long
Dim i As Long
Dim ws As Worksheet
i = 3
Application.ScreenUpdating = False
With ActiveSheet
loLetzte = .Cells(.Rows.Count, 2).End(xlUp).Row
Set raBereich = .Range(.Cells(3, 1), .Cells(loLetzte, 2))
raBereich.ClearContents
End With
For Each ws In ThisWorkbook.Sheets
ActiveSheet.Cells(1, 1) = "Inhaltsverzeichnis"
ActiveSheet.Cells(i, 1) = i - 2
ActiveSheet.Columns(1).NumberFormat = "0""."""
ActiveSheet.Cells(i, 2) = ws.Name
i = i + 1
Next ws
With ActiveSheet
loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
Set raBereich = .Range(.Cells(3, 2), .Cells(loLetzte, 2))
For Each raZelle In raBereich
If raZelle = .Name Then
.Range(.Cells(raZelle.Row, 1), .Cells(raZelle.Row, 2)).Font.Color = vbRed
Else
.Range(.Cells(raZelle.Row, 1), .Cells(raZelle.Row, 2)).Font.Color = vbBlack
End If
Next raZelle
.Columns(1).ColumnWidth = 5
.Columns(2).AutoFit
End With
Set raBereich = Nothing
Application.ScreenUpdating = True
End Sub
Gruß Werner
Anzeige
Füg mal ein Diagramm in die Mappe ein ... owT
25.05.2017 18:59:47
Matthias
AW: Füg mal ein Diagramm in die Mappe ein ... owT
25.05.2017 21:01:09
Martin
Was meinst du mit dem Diagramm?
Danke
25.05.2017 20:53:40
Martin
Danke Werner, das sieht richtig gut aus.
Falls du mal Zeit haben sollest, würde es mich sehr freuen, wenn du auch die Hyperlinks einbauen könntest und eine Standardschrift Arial in Größe 12.
Danke vielmals für deine Hilfe!
Gruß
Martin
AW: Ideen ?
25.05.2017 22:00:13
Martin
Werner, hallo nochmal!
Ich habe einen Code gefunden mit den Hyperlinks, allerdings wird ein Verzeichnis in einem separaten Tabellenblatt erstellt.
Da ich mich mit VBA kaum auskenne, kriege ich den Code mit deinem nicht vereint.

Sub MappenInhaltZusammenstellen()
Dim Tabelle As Worksheet
Dim i As Integer
Worksheets.Add.Move before:=Worksheets(1)
ActiveSheet.Name = "Inhalt"
Cells(2, 2).Value = "Enthaltene Blätter"
i = 3
For Each Tabelle In ActiveWorkbook.Worksheets
If Tabelle.Name  "Inhalt" Then
Cells(i, 2).Value = Tabelle.Name
Tabelle.Hyperlinks.Add Anchor:=Cells(i, 2), Address:="", SubAddress:= _
Tabelle.Name & "!A1", ScreenTip:="Hyperlink klicken", _
TextToDisplay:=Tabelle.Name
i = i + 1
End If
Next Tabelle
End Sub
Kannst du mir dabei helfen?
Gruß
Martin
Anzeige
AW: Ideen ?
26.05.2017 13:51:33
Werner
Hallo Martin,
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim raBereich As Range
Dim raZelle As Range
Dim loLetzte As Long
Dim i As Long
Dim ws As Worksheet
i = 3
Application.ScreenUpdating = False
With ActiveSheet
loLetzte = .Cells(.Rows.Count, 2).End(xlUp).Row
Set raBereich = .Range(.Cells(3, 1), .Cells(loLetzte, 2))
raBereich.ClearContents
End With
For Each ws In ThisWorkbook.Worksheets
ActiveSheet.Cells(1, 1) = "Inhaltsverzeichnis:"
ActiveSheet.Cells(1, 1).Font.Size = 12
ActiveSheet.Cells(i, 1) = i - 2
ActiveSheet.Columns(1).NumberFormat = "0""."""
ActiveSheet.Cells(i, 2) = ws.Name
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 2), Address:="", SubAddress:= _
ActiveSheet.Cells(i, 2).Value & "!A1", ScreenTip:="Hyperlink klicken", _
TextToDisplay:=ActiveSheet.Cells(i, 2).Value
i = i + 1
Next ws
With ActiveSheet
loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
Set raBereich = .Range(.Cells(3, 2), .Cells(loLetzte, 2))
For Each raZelle In raBereich
If raZelle = .Name Then
.Range(.Cells(raZelle.Row, 1), .Cells(raZelle.Row, 2)).Font.Size = 12
.Range(.Cells(raZelle.Row, 1), .Cells(raZelle.Row, 2)).Font.Color = vbRed
Else
.Range(.Cells(raZelle.Row, 1), .Cells(raZelle.Row, 2)).Font.Size = 12
.Range(.Cells(raZelle.Row, 1), .Cells(raZelle.Row, 2)).Font.Color = vbBlack
End If
Next raZelle
.Columns(1).ColumnWidth = 5
.Columns(2).AutoFit
End With
Set raBereich = Nothing
Application.ScreenUpdating = True
End Sub
Gruß Werner
Anzeige
Fehler
27.05.2017 02:02:35
Martin
Hallo Werner,
bis auf ein paar Fehler funktioniert der Code super.
Wenn ich auf einige Hyperlinks klicke, wie z.B. 9,28,29,31 kommt ein Fehler.
Außerdem ist die Formatierung nicht einheitlich.
Könntest du folgende Formatierung übernehmen:
* Ganzes Verzeichnis (Spalten A-B):
--- Schrift: Arial
--- Schriftgröße: 12 (funktioniert bereits)
--- nicht kursiv und nicht fett
--- Hintergrundfarbe: hell-grau
--- Spalte A mit Nummerierung: mittig
--- Spalte B mit Hyperlinks: linksbündig (im Moment ist es mal linksbündig, mal mittig)
* Überschrift "Inhaltsverzeichnis" (Zellen A1-B1):
--- fett
--- Hintergrundfarbe: dunkel-grau
--- Schriftfarbe: weiß
Wenn man mit dem Cursor auf ein Link führt erscheint ein Infotext "Hyperlink klicken". Kann ich für jedes Hyperlink selbst definieren, was für ein Text als Info erscheint?
Zum Thema Cursor: Meinst du, du könntest noch die Zellen (A-B) fett stellen oder mit einer Hintergrundfarbe markieren, über die der Cursor geht?
Gruß
Martin
Anzeige
AW: Fehler
27.05.2017 07:04:21
Matthias
Hallo,
warum Werner nicht auf meinen Hinweis reagiert ist mir ein Rätsel.
Ich hatte ja bereits darauf hingewiesen, das beim Einfügen eines Diagramm(Registers)
der Code in einen VBA-Fehler läuft.
Grund sind diese Zeilen:

With ActiveSheet
loLetzte = .Cells(.Rows.Count, 2).End(xlUp).Row
Set raBereich = .Range(.Cells(3, 1), .Cells(loLetzte, 2))
raBereich.ClearContents
End With
Wenn man also z.B ein Diagramm als Register einfügt, ist das Diagramm das ActiveSheet
Ein Diagramm hat aber keine "Zellen" (Cells).
Somit geht der Code zwangsläufig in einen Laufzeitfehler(438), den man abfangen muss.
Das kannst Du ja nun mal Testen. Ich hatte ja bereits am 25.5 darauf hingewiesen.
Eine Möglichkeit das zu umgehen. Setze folgende Zeile gleich unter die letzte Dim-Anweisung
If TypeName(ActiveSheet)  "Worksheet" Then Exit Sub
Gruß Matthias
Anzeige
AW: Sry...
27.05.2017 09:10:16
Werner
Hallo Matthias,
gelesen habe ich deinen Hinweis, aber dann bei der Antwort an Martin irgenwie die entsprechende Reaktion darauf verpeilt.
Sorry, bin nämlich für derartige Hinweise sehr dankbar, weil ich in Sachen VBA gerade mal über die nötigsten Grundkenntnisse raus bin.
Von daher nochmals ein Danke für deine Hinweise. Versuche sie bei der nächsten Antwort an Martin zu berücksichtigen.
Gruß Werner
schön zu wissen ...
27.05.2017 10:27:08
Matthias
Hallo
... das Du dankbar bist (mir geht es nämlich auch immer so).
Dachte schon Du nimmst mir meinen Beitrag übel.
Also ist alles gut :-)
& ein SchöWE für alle.
Gruß Matthias
Anzeige
danke für den Hinweis
27.05.2017 11:31:39
Martin
Guten Morgen zusammen,
danke Mathias, wäre mir nicht mal aufgefallen, weil ich noch kein Diagramm-Register habe.
Finde es echt klasse, wie aufmerksam ihr Helfer seid und euch auch gegenseitig unterstützt. Danke, dass es euch gibt!
Bin schon ganz gespannt auf den nächsten Lösungsvorschlag von Werner :-)
P.S.: Deine "VBA-Grundkenntnisse" hätte ich gerne - haha, für mich bist du ein Profi
hier meine BspDatei ...
27.05.2017 16:16:02
Matthias
Hallo
Da ich nicht sicher bin ob Werner noch dran ist am Problem, hier mein Vorschlag
der nur Werners Code ergänzt.
https://www.herber.de/bbs/user/113825.xlsm
Gruß Matthias
Anzeige
Fehler
27.05.2017 17:25:15
Martin
Hi Matthias,
danke für die Ergänzung und die Kommentare im Code! :-)
Die Formatierung sieht super aus!
Ein paar Sachen laufen noch nicht rund:
Leider gibt es nach wie vor Fehler, wenn man einige Hyperlinks anklickt.
Die Fehlermeldung, die am meisten vorkommt: "Bezug ist ungültig".
1 x kam dieser Fehler: "Der eingegebene Text ist kein gültiger Bezug oder kein definierter Name".
Was auch komisch ist, dass die Überschrift "Inhaltsverzeichnis in A1 nicht komplett gezeigt wird. Ich sehe nur "Verzeichnis". Woran liegt es?
Bei einigen Hyperlinks dauert es bis zu 5 sek bis man zum jeweiligen Tabellenblatt gelangt. Ist es nur bei mir so?
Gruß
Martin
AW: Fehler
27.05.2017 17:47:52
Matthias
Hallo
Das liegt daran das Werner hier diese Zeile eingebaut hat
.Columns(1).ColumnWidth = 5
Du musst also in diesem Bereich:
auch Spalte(1) bedienen

.Columns(1).AutoFit
.Columns(2).AutoFit
Oder Du verbindest A1:B1 zu einer Verbundzelle, wovor Einige/Viele immer wieder warnen.
Das mit dem ungültigen Bezug bin ich noch am tüffteln.
Gruß Matthias
Textausrichtung
27.05.2017 18:04:48
Martin
Das mit der Spaltenbreite 5 ist schon ok so. Ich denke, dass das Problem sich beheben lässt, wenn man die Zelle A1 auf linksbündig stellt. Kannst du mir den VBA-Code dazu sagen?
Textausrichtung
27.05.2017 18:06:04
Martin
Das mit der Spaltenbreite 5 ist schon ok so. Ich denke, dass das Problem sich beheben lässt, wenn man die Zelle A1 auf linksbündig stellt. Kannst du mir den VBA-Code dazu sagen?
liest Du auch den Code mal durch ?
27.05.2017 18:16:22
Matthias
Hallo

Columns(1).HorizontalAlignment = xlCenter 'mittig
Columns(2).HorizontalAlignment = xlLeft   'links
Ich überlasse es nun echt Deiner Intelligenz
Links für Spalte(1) anzupassen.

.Columns(1).AutoFit
.Columns(2).AutoFit
gehört trotzdem rein!
Gruß Matthias
AW: liest Du auch den Code mal durch ?
27.05.2017 18:30:33
Martin
Ja, das tue ich! Und soweit ich den Code richtig lesen kann, steht hier linksseitig für Spalte B!
Mittige Ausrichtung steht für die komplette Spalte A.
Ich hätte gerne aber eine linksseitige Ausrichtung für A1, damit das komplette Wort "Inhaltsverzeichnis" angezeigt wird.
Wenn ich "AutoFit" auch für die erste Spalte einstelle, dann sieht es nicht gut aus. Die Spalte wird unnötig breit.
AW: A1
27.05.2017 18:52:53
Gerd
Weil ich Range("A1") und [A1] sowieso nicht mag, nehme ich in der Regel Cells(1, 1) um auf A1 zu referenzieren.
Gruß Gerd
Thx!
27.05.2017 19:05:19
Martin
Danke Gerd für den Hinweis, damit habe ich das Problem behoben:
Folgende Zeile vor dem letzten "End With" eingefügt:
    .Cells(1, 1).HorizontalAlignment = xlLeft   'links

Range("A1") und [A1] hab ich auch nicht benutzt oT
27.05.2017 19:10:50
Matthias
AW: Weiterhin frohes Schaffen!
27.05.2017 20:01:07
Gerd
2 weitere Fehler
27.05.2017 18:25:29
Martin
Mir ist noch aufgefallen, dass der letzte Hyperlink immer in der Schriftart "MS Sans Serif" statt Arial erscheint.
Und wenn man ein Diagramm-Reiter hinzufügt, dann wird kein Hyperlink im Inhaltsverzeichnis dazu erzeugt.
AW: Bin noch dran..
28.05.2017 16:45:52
Werner
Hallo Martin und an die Helfer,
..da ich aber im Schichtdienst bin und die Ehre hatte an diesem WE zu arbeiten, melde ich mich erst heute wieder.
Ich habe am letzten Code von Matthias noch ein paar kleine Änderungen vorgenommen, so dass auch Diagrammblätter im Inhaltsverzeichnis angezeigt werden.
Allerdings bekomme ich es nicht hin, mit dem entsprechenden Hyperlink das Diagrammblatt auszuwählen. Läuft in eine Fehlermeldung, dass ein Fehler in einem Zellbezug nicht stimmt. Ist ja auch logisch, da im Hyperlink ja auf eine Zelle verwiesen wird, ein Diagrammblatt aber keine Zellen hat.
Ich habe keine Ahnung, ob das überhaupt geht, per Hyperlink ein Diagrammblatt auszuwählen. Auch stöbern im Archiv hat mich nicht weiter gebracht.
Geht das überhaupt? Ich kriegs auf alle Fälle nicht hin - was aber nicht wirklich viel bedeutet.
Aus diesem Grund verzichte ich vorläufig mal den Code einzustellen.
Gruß Werner
AW: Bin noch dran..
28.05.2017 17:04:22
Werner
Hallo Martin,
ich habe da noch mal an einer anderen Version mit einer Userform und einer Listbox gebastelt. Das würde dann auch deinem Wunsch entsprechen, dass das Inhaltsverzeichnis sich nicht verändert wenn du Daten filterst.
Schau dir die Beispielmappe mal an. Mit den Einstellungen der Werte Top und Left müsstest du das dann an deine Bildschirmauflösung anpassen, damit die Userform auf dem Tabellenblatt da ist, wo du sie haben möchtest.
Du kannst das Inhaltsverzeichnis mit dem Butto auf der Userform schließen. Sobald die Datei neu gestartet wird, oder du auf ein anderes Tabellenblatt wechselst, wird das Inhaltsverzeichnis wieder geöffnet.
https://www.herber.de/bbs/user/113838.xlsm
Ich lass trotzdem mal offen, wegen meiner Frage zu Hyperlink auf Diagrammblätter.
Gruß Werner
funktioniert doch, auch mit Diagrammen :-) oT
28.05.2017 17:33:55
Matthias
Meinst du die Version mit Userform?
28.05.2017 18:36:58
Werner
Hallo Matthias,
falls ja, die funktioniert.
Aber die Version mit den Hyperlinks funktioniert nicht. Ich kriegs nicht hin per Hyperlink auf das Daigrammblatt zu wechseln.
https://www.herber.de/bbs/user/113840.xlsm
Gruß Werner
fast perfekt
28.05.2017 18:56:29
Martin
Hallo Werner,
schön von dir zu lesen und danke, dass du deine Zeit für mich investierst! Kann da nicht genug danken :-)
Erstmal: WOW! Das so eine Useform überhaupt möglich ist! Respekt!
Leider habe ich immer noch Probleme mit den Hyperlinks, ich weiß allerdings nicht, ob es die gleichen sind wie bei der vorherigen Lösung. Ich habe Tabellenblätter in dieser Reihenfolge:
Tabelle1 Tabelle2 Tabelle
Wenn ich nun auf Tabelle klicke, dann zeigt mir Excel Hyperlink von Tabelle1. Also liegt es wohl an dem Namen des Tabellenblatts?
Der zweite Fehler tritt ein, wenn die Form relativ klein ist und diese gescrollt werden muss. Wenn ich auf ein Tabellenblatt klicke, welches im Verzeichnis so nicht zu sehen ist und erst nach Scrollen sichtbar wäre, dann zeigt mir Excel einen faschen Hyperlink in der Box. Total merkwürdig.
Das Platzieren der Form mittels "Left" und "Top" klappt auch nicht. Ich kann die Form aber durch das Anklicken verschieben. Kann man die Breite der Form an die Textlänge der Hyperlinks anpassen und die Länge mit einer Angabe im Code fest machen?
Kurze Frage noch: Der Code in "DieseArbeitsmappe" steht 2x drin, nur die erste Zeile ist anders. Der Code funktioniert aber auch nur mit dem unteren Code. Wofür ist der erste Teil?
Übrigens, bei mir wird auch das Hyperlink auf das Diagrammblatt angezeigt :-)
Gruß
Martin
AW: fast perfekt
28.05.2017 19:09:15
Werner
Hallo Martin,
ich habe keine Ahnung was du jetzt mit Hyperlink meinst. Bei dieser Beispielmappe ist absolut nichts mit irgendwelchen Hyperlinks drin. Es werden alle Blattnamen in die Listbox eingelesen und bei klick auf einen der Blattnamen in der Listbox wird das entsprechende Blatt aktiviert.
Zu deinen anderen Problemen kann ich absolut nichts sagen, bei mir funktioniert es problemlos.
Da solltest du mal deine Beispielmappe hochladen. Aber bitte eine, bei der es nicht funktioniert. Daten brauchen auf den Blättern keine zu sein.
Gruß Werner
AW: fast perfekt
28.05.2017 19:55:38
Martin
Ich habe die beiden Fehler auch in deiner Datei bekommen:
1. Problem:
Verändere den Namen eines Tabellenblatts (TB). Statt TB 5, schreibe nur TB.
Klicke nun das Hyperlink zu TB 5 an, du wirst bei dem TB1 landen und nicht TB5. Bei dem Hyperlink wird anscheinend nicht der komplette Name des TB gesucht, sondern ein Teil.
2. Problem:
Füge deiner Mappe mehr TB ein, bspw. bis TB 20.
Wenn du beispielsweise Hyperlink für TB 20 anklickst, dann landest du nicht bei TB 20. Wenn die Form aber länger wäre und das Hyperlink ohne das Scrollen zu sehen wäre, dann wäre man bei dem richtigen TB gelandet.
AW: fast perfekt
29.05.2017 11:52:10
Werner
Hallo Martin,
noch mal zum besseren Verständnis. In der Version mit der Userform auf dem Tabellenblatt gibt es keine Hyperlinks. Du meinst hier Listboxeinträge.
Das Problem mit dem aktivieren der falschen Blätter hing/hängt mit dem Click-Ereignis der Listbox zusammen, warum auch immer.
Das habe ich jetzt folgendermaßen angepasst: Ein Wechsel auf das entsprechende Blatt erfolgt jetzt, wenn du auf den Eintrag in der Listbox einen Doppelklick ausführst. Oder du wählst einen Eintrag mit einem einfachen Klick aus und bestätigst dann mit der Enter-Taste.
Das funktioniert jetzt auch wenn du in der Listbox mehr Einträge stehen hast, wie angezeigt werden können.
Das Problem mit dem Ändern eines Blattnamens habe ich so gelöst: Wenn du den Namen eines Tabellenblattes änderst, dann wird die Listbox aktualisiert, sobald du mit der Maus in die Listbox gehst. Eine Aktualisierung ergolgt auch, wenn du das Tabellenblatt wechselst (war aber vorher schon so).
Bei der ersten Version, ohne Userform und mit Hyperlinks, bin ich jetzt so weit, dass zwar eine Fehlermeldung angezeigt wird sobald der Hyperlink eines Diagrammblattes angeklickt wird, wird die Fehlermeldung bestätigt, dann wird trotzdem richtigerweise auf das Diagrammblatt gewechselt.
Diese Fehlermeldung kriege ich leider nicht weg.
Auch bei dieser Version besteht das Problem, dass sich das Inhaltsverzeichnis nicht automatisch aktualisiert, wenn ein Blattname geändert wird. Erst wenn du auf ein anderes Tabellenblatt wechselst, wird die Liste aktualisiert. Mir ist kein Worksheet Ereignis bekannt, das auf die änderung des Blattnamens reagiert.
Beiliegend mal beide Versionen.
https://www.herber.de/bbs/user/113854.xlsm
https://www.herber.de/bbs/user/113855.xlsm
Gruß Werner
Fehler
27.05.2017 17:25:22
Martin
Hi Matthias,
danke für die Ergänzung und die Kommentare im Code! :-)
Die Formatierung sieht super aus!
Ein paar Sachen laufen noch nicht rund:
Leider gibt es nach wie vor Fehler, wenn man einige Hyperlinks anklickt.
Die Fehlermeldung, die am meisten vorkommt: "Bezug ist ungültig".
1 x kam dieser Fehler: "Der eingegebene Text ist kein gültiger Bezug oder kein definierter Name".
Was auch komisch ist, dass die Überschrift "Inhaltsverzeichnis in A1 nicht komplett gezeigt wird. Ich sehe nur "Verzeichnis". Woran liegt es?
Bei einigen Hyperlinks dauert es bis zu 5 sek bis man zum jeweiligen Tabellenblatt gelangt. Ist es nur bei mir so?
Gruß
Martin
Fehler
27.05.2017 17:25:37
Martin
Hi Matthias,
danke für die Ergänzung und die Kommentare im Code! :-)
Die Formatierung sieht super aus!
Ein paar Sachen laufen noch nicht rund:
Leider gibt es nach wie vor Fehler, wenn man einige Hyperlinks anklickt.
Die Fehlermeldung, die am meisten vorkommt: "Bezug ist ungültig".
1 x kam dieser Fehler: "Der eingegebene Text ist kein gültiger Bezug oder kein definierter Name".
Was auch komisch ist, dass die Überschrift "Inhaltsverzeichnis in A1 nicht komplett gezeigt wird. Ich sehe nur "Verzeichnis". Woran liegt es?
Bei einigen Hyperlinks dauert es bis zu 5 sek bis man zum jeweiligen Tabellenblatt gelangt. Ist es nur bei mir so?
Gruß
Martin
Fehler noch da
27.05.2017 17:26:21
Martin
Hi Matthias,
danke für die Ergänzung und die Kommentare im Code! :-)
Die Formatierung sieht super aus!
Ein paar Sachen laufen noch nicht rund:
Leider gibt es nach wie vor Fehler, wenn man einige Hyperlinks anklickt.
Die Fehlermeldung, die am meisten vorkommt: "Bezug ist ungültig".
1 x kam dieser Fehler: "Der eingegebene Text ist kein gültiger Bezug oder kein definierter Name".
Was auch komisch ist, dass die Überschrift "Inhaltsverzeichnis in A1 nicht komplett gezeigt wird. Ich sehe nur "Verzeichnis". Woran liegt es?
Bei einigen Hyperlinks dauert es bis zu 5 sek bis man zum jeweiligen Tabellenblatt gelangt. Ist es nur bei mir so?
Gruß
Martin
AW: Fehler noch da
27.05.2017 17:30:09
Martin
sorry, war keine Absicht mehrfach zu antworten, mein Internet spinnt wohl
Fehler noch da
27.05.2017 17:26:34
Martin
Hi Matthias,
danke für die Ergänzung und die Kommentare im Code! :-)
Die Formatierung sieht super aus!
Ein paar Sachen laufen noch nicht rund:
Leider gibt es nach wie vor Fehler, wenn man einige Hyperlinks anklickt.
Die Fehlermeldung, die am meisten vorkommt: "Bezug ist ungültig".
1 x kam dieser Fehler: "Der eingegebene Text ist kein gültiger Bezug oder kein definierter Name".
Was auch komisch ist, dass die Überschrift "Inhaltsverzeichnis in A1 nicht komplett gezeigt wird. Ich sehe nur "Verzeichnis". Woran liegt es?
Bei einigen Hyperlinks dauert es bis zu 5 sek bis man zum jeweiligen Tabellenblatt gelangt. Ist es nur bei mir so?
Gruß
Martin
Fehler noch da
27.05.2017 17:29:34
Martin
Hi Matthias,
danke für die Ergänzung und die Kommentare im Code! :-)
Die Formatierung sieht super aus!
Ein paar Sachen laufen noch nicht rund:
Leider gibt es nach wie vor Fehler, wenn man einige Hyperlinks anklickt.
Die Fehlermeldung, die am meisten vorkommt: "Bezug ist ungültig".
1 x kam dieser Fehler: "Der eingegebene Text ist kein gültiger Bezug oder kein definierter Name".
Was auch komisch ist, dass die Überschrift "Inhaltsverzeichnis in A1 nicht komplett gezeigt wird. Ich sehe nur "Verzeichnis". Woran liegt es?
Bei einigen Hyperlinks dauert es bis zu 5 sek bis man zum jeweiligen Tabellenblatt gelangt. Ist es nur bei mir so?
Gruß
Martin
Löschen von Registern auch berücksichtigen!
25.05.2017 17:14:02
Registern
Hallo,
bitte auch das Löschen von Registern berücksichtigen!
Der Bereich sollte also voher auch geleert werden.
Wenn z.B. 3 Register vorhanden sind und das Zweite wird gelöscht,
so wird 2x das 3.Register aufgelistet.
Nur mal so als Hinweis
Gruß Matthias
AW: ohne "Schnickschnack" Formellösung möglich...
25.05.2017 19:05:20
...
Hallo Martin,
... mit "Schnickschnack" meinte ich, dass der jeweilige Links fetter bzw. ein Vorschaufenster eingeblendet wird, wenn die Maus über die Zelle "streicht". Das kann natürlich durch eine Formel nicht realisiert werden.
Basierend auf den von WF angegebenen Link: http://www.excelformeln.de/formeln.html?welcher=392 lässt sich das Inhaltsverzeichnis relativ einfach aufbauen. Eine Zellformel und zwei benannte Formeln sind ausreichend.
Ein Klick auf die jeweilige Zelle und Du landest im jeweiligen Tabellenblatt (momentan fix immer in A1, was sich aber auch leicht in/mit der Zellformel ändern lässt).
Die Tabellenblätter kannst Du beliebig verschieben, löschen und ergänzen (die aufgezeigte Formel B2 muss zuvor lediglich weit genug nach unten kopiert sein).
Das Inhaltsverzeichnis beginnt immer in Zelle 2 und kann in jedes Tabellenblatt kopiert werden, die Spalte ist egal.
Die Datei muss als XLSM oder XLSB abgespeichert werden.
 AB
1 Inhalt
2 Tabelle1
3 irgendwas
4 Tabelle3
5 25.05.17
6 letztes Blatt
7  

Formeln der Tabelle
ZelleFormel
B2=WENNFEHLER(HYPERLINK("#'"&Blatt&"'!A1";Blatt); "")
Namen in Formeln
ZelleNameBezieht sich auf
B2Blatt=TEIL(INDEX(Tab;ZEILE()-1);FINDEN("]";INDEX(Tab;ZEILE()-1))+1;31)
B2Tab=ARBEITSMAPPE.ZUORDNEN(1+0*JETZT())
Namen verstehen

Gruß Werner
.. , - ...
AW: ohne "Schnickschnack" Formellösung möglich...
25.05.2017 21:00:12
Martin
Danke, das habe ich schon ausprobiert, aber mit VBA ist es etwas praktischer, wenn man öfters Blätter hinzufügt, und via VBA kann man die "Schnickschnacks" einbauen :)
VBA-Code von Werner => Leerzeichen akzeptieren
29.05.2017 10:23:35
Werner
Hi Werner,
hab eine kurze Frage zu deinem VBA-Code aus der 1. Version (also die Version vor der Userform-Version, die Matthias ergänzt hat):

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim raBereich As Range
Dim raZelle As Range
Dim loLetzte As Long
Dim i As Long
Dim ws As Worksheet
'Abbruch wenn ActiveSheet kein WorkSheet ist
If TypeName(ActiveSheet)  "Worksheet" Then Exit Sub
i = 3
Application.ScreenUpdating = False
With ActiveSheet
loLetzte = .Cells(.Rows.Count, 2).End(xlUp).Row
Set raBereich = .Range(.Cells(3, 1), .Cells(loLetzte, 2))
raBereich.ClearContents
End With
For Each ws In ThisWorkbook.Worksheets
With ActiveSheet
.Cells(1, 1) = "Inhaltsverzeichnis:"
.Cells(1, 1).Font.Size = 12
.Cells(i, 1) = i - 2
With .Columns("A:B")
.Interior.ColorIndex = 15
.Font.Name = "Arial"
.Font.Italic = False
.Font.Bold = False
End With
With Range("A1:B1")
.Font.Bold = True           'fett
.Interior.ColorIndex = 16
.Font.Color = vbWhite
End With
Columns(1).HorizontalAlignment = xlCenter 'mittig
Columns(2).HorizontalAlignment = xlLeft   'links
.Columns(1).NumberFormat = "0""."""
.Cells(i, 2) = ws.Name
'.Hyperlinks.Add Anchor:=Cells(i, 2), Address:="", SubAddress:=.Cells(i, 2).Value & "!A1",  _
ScreenTip:="Hyperlink klicken", TextToDisplay:=.Cells(i, 2).Value
.Hyperlinks.Add Anchor:=Cells(i, 2), Address:="", SubAddress:=.Cells(i, 2).Value & "!A1",  _
ScreenTip:="klick hier um ins Register [" & .Cells(i, 2).Value & "] zu gelangen", TextToDisplay:=.Cells(i, 2).Value
End With
i = i + 1
Next ws
With ActiveSheet
loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row
Set raBereich = .Range(.Cells(3, 2), .Cells(loLetzte, 2))
For Each raZelle In raBereich
If raZelle = .Name Then
.Range(.Cells(raZelle.Row, 1), .Cells(raZelle.Row, 2)).Font.Size = 12
.Range(.Cells(raZelle.Row, 1), .Cells(raZelle.Row, 2)).Font.Color = vbRed
Else
.Range(.Cells(raZelle.Row, 1), .Cells(raZelle.Row, 2)).Font.Size = 12
.Range(.Cells(raZelle.Row, 1), .Cells(raZelle.Row, 2)).Font.Color = vbBlack
End If
Next raZelle
.Columns(1).ColumnWidth = 5
.Columns(2).AutoFit
End With
Set raBereich = Nothing
Application.ScreenUpdating = True
End Sub
Ich habe mal gepostet, dass einige Hyperlinks bei mir falsch anspringen, sprich man gelangt zu einem anderen Tabellenblatt (bspw. klickt man auf Hyperlink von Tabellenblatt 6, gelangt aber zum Tabellenblatt 2).
Ein Grund ist wohl ein Leerzeichen in einigen meiner Tabellenblattnamen. Sorry, ich weiß, dass man das meiden sollte, aber kann man das Leerzeichen mit VBA-Code akzeptieren lassen?
Gruß
Martin

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige