Hyperlinks in VBA Inhaltsverzeichnis

[Katja213]

Hallo,

ich bastel gerade an einer Excel-Datei mit etwa 70 Tabellenblättern und würde gerne für die bessere Übersicht als erstes Tabellenblatt ein Inhaltsverzeichnis einfügen, das man durch Knopfdruck automatisch aktualisieren kann. Das funktioniert auch soweit gut, ich habe es geschafft die Stammdaten für jedes Tabellenblatt auf dem jeweiligen Blatt eintragen und ins Inhaltsverzeichnis kopieren zu lassen. Jetzt würde ich gerne aber noch einen Hyperlink zufügen, der einem erlaubt, auf den Namen zu klicken und direkt in das jeweilige Tabellenblatt zu springen. Das habe ich aber noch nicht geschafft. Wenn mir jemand helfen könnte, was ich an der Stelle falsch gemacht habe, das wäre super!

Viele Grüße
Katja

Code:

Sub Inhaltsverzeichnis()

Dim i As Integer
Dim Inhalt As Worksheet

Set Inhalt = Worksheets("Inhalt")

' Bestehenden Inhalt löschen:
For x = 3 To 200
    ' Statt 3 To 200 könnte man auch 3 To Rows.Count eingeben, dann lädt das Makro aber relativ lange, da es alle Zeilen durchsucht. 200 wurde willkürlich als "hohe Zahl" festgelegt.
Inhalt.Rows(x).ClearContents
Next x

' Für jedes Tabellenblatt durchführen, ab dem zweiten bis zum letzten:
For i = 2 To Worksheets.Count

' Den Bereich definieren, der in das Inhaltsverzeichnis reinkopiert werden soll:
Set BereichZielTab = Worksheets(i).Range("A3,A5,A7,A9")
' Den Bereich im Inhaltsverzeichnis definieren, wo die kopierten Informationen eingefügt werden sollen:
' Es soll unter der letzten Zelle, die etwas enthält, eingefügt werden
Set LetzteZeileInhalt = Worksheets(1).Cells(Rows.Count, "A").End(xlUp)(2)

' Jetzt die definierten Informationen kopieren, Formatierung entfernen und transponiert an der definierten Stelle einfügen
BereichZielTab.Copy
LetzteZeileInhalt.PasteSpecial Paste:=xlPasteValues, Transpose:=True

' Jetzt einen Hyperlink einsetzen, mit dem direkt zum richtigen Tabellenblatt gewechselt werden kann (funktioniert noch nicht):
'LetzteZeile = Inhalt.UsedRange.SpecialCells(xlCellTypeLastCell).Row
'ActiveSheet.Hyperlinks.Add Range("LetzteZeile"), Address:="", SubAddress:="'" & Worksheets(i).Name & "!A1"

' Dasselbe Vorgehen auch für das nächste Tabellenblatt durchführen
Next i

End Sub

[snb]

Integriere alle Daten in einem Arbeitsblatt
Verwende das zweite Arbeitsblatt als Filter "pro sheet'.
Wenn du 3 anonymisierte Sheets hochlädest, zeige ich mal wie ich das meine.

Deine Frage:

Code:

Sub M_snb()
  For Each it In Sheets
    n = n + 1
    it.Hyperlinks.Add Cells(n, 1), "", it.Cells(1).Address(, , , True),,it.name
  Next
End Sub

[schauan]

Hallöchen,

ich gebe Dir erst mal eine Formellösung

1) definiere einen Namen z.B Tabellen mit der Funktion:
=TEXTNACH(ARBEITSMAPPE.ZUORDNEN(1);"]")

2)
trage auf dem Blatt diese Formel ein:
=HYPERLINK("#" & WECHSELN(MTRANS(Tabellen)&JETZT();JETZT();"") & "!A1")

[Jockel]

Hallo,

ich bastel gerade an einer Excel-Datei mit etwa 70 Tabellen...

Viele Grüße
Katja

Servus Katja, eine Zwitter-Lösung(Formel und Makro(Makro4) wäre das hier..: 
Wobei "ordner" vielleicht besser durch Blatt oder Register ersetzt werden sollte...

Arbeitsblatt mit dem Namen 'Inhalt'
	B
4	filter_1
5	paraverbale Kommunikation
6	Ferien_26
7	Kalgr
8	Tabelle3

Name	Bezug
ordner	=ARBEITSMAPPE.ZUORDNEN(1+0*JETZT())

Zelle	Formel
B4	=WENN(ZEILE(A2)>ANZAHL2(ordner);"";HYPERLINK("#'"&INDEX(ordner;ZEILE(A2))&"'!A1"; TEIL(INDEX(ordner;ZEILE(A2));FINDEN("]";INDEX(ordner;ZEILE(A2)))+1;31)))
B5	=WENN(ZEILE(A3)>ANZAHL2(ordner);"";HYPERLINK("#'"&INDEX(ordner;ZEILE(A3))&"'!A1"; TEIL(INDEX(ordner;ZEILE(A3));FINDEN("]";INDEX(ordner;ZEILE(A3)))+1;31)))
B6	=WENN(ZEILE(A4)>ANZAHL2(ordner);"";HYPERLINK("#'"&INDEX(ordner;ZEILE(A4))&"'!A1"; TEIL(INDEX(ordner;ZEILE(A4));FINDEN("]";INDEX(ordner;ZEILE(A4)))+1;31)))
B7	=WENN(ZEILE(A5)>ANZAHL2(ordner);"";HYPERLINK("#'"&INDEX(ordner;ZEILE(A5))&"'!A1"; TEIL(INDEX(ordner;ZEILE(A5));FINDEN("]";INDEX(ordner;ZEILE(A5)))+1;31)))
B8	=WENN(ZEILE(A6)>ANZAHL2(ordner);"";HYPERLINK("#'"&INDEX(ordner;ZEILE(A6))&"'!A1"; TEIL(INDEX(ordner;ZEILE(A6));FINDEN("]";INDEX(ordner;ZEILE(A6)))+1;31)))

Verwendete Systemkomponenten: [Windows (64-bit) NT 10.00] / MS Excel 365

Diese Tabelle wurde mit Tab2Html (v2.7.1) erstellt. ©Gerd alias Bamberg

Das nutze ich selber bei allen neueren Dateien.
Reagiert auf Umbennenung und/oder Verschieben von Tabellenblättern

[slowboarder]

Hi

ActiveSheet.Hyperlinks.Add Range("LetzteZeile"), Address:="", SubAddress:="'" & Worksheets(i).Name & "!A1"

Da sind jetzt zwei Fehler drin.
zum einen fehlt das Hochkomma nach dem Sheetnamen in der SubAddress
richtig wäre

..., SubAddress:="'" & Worksheets(i).Name & "'!A1"

auch bei der Zielzelle machst du einen Fehler.
richtig wäre hier:
...Add Range("E" & LetzteZeile)...

wobei es jedoch besser wäre, die Zeile nicht neu zu ermitteln, sondern die Zelle, die du schon als Zielzeile für diese Tabellenblatt ermittelt hast, nochmal anzuwenden:
...Add LetzteZeileInhalt.Offset(0, 4), ...

also nochmal als ganzes:

Code:

ActiveSheet.Hyperlinks.Add LetzteZeileInhalt.Offset(0, 4), Address:="", SubAddress:="'" & Worksheets(i).Name & "'!A1"

Was noch so auffällt:
für das Löschen der Inhalte brauchst du keine Schleife, das geht auch in einem Schritt für mehrere Zeilen. Dann spielt es auch keine Rolle, wieviele Zeilen das sind

Code:

Inhalt.Range("2:200").ClearContents

Außerdem solltest du Option Explicit anwenden und deine Variablen deklarieren.
Warum, steht hier:
Online - Excel Programmierung VBA Makro: Option Explicit, Intellisense und Techniken des Schreibens
Gruß Daniel

[schauan]

Hallöchen,

Da sind jetzt zwei Fehler drin.

ich reduziere aus meiner Sicht mal auf einen (und man kann zwei Dinge besser machen / oder drei )

kein Fehler:
Hochkommas braucht man nur, wenn die Blattnamen es erfordern. Mit Hochkommas ist man natürlich auf der sicheren Seite und ich auf der von Daniel Sollte man auch bei meinem Formelvorschlag bedenken ...


Range("E" & LetzteZeile)
bzw.
... LetzteZeileInhalt.Offset

da würde ich empfehlen, bessere Variablennamen zu benutzen Rein sprachlich betrachtet kann man auf einen Inhalt keinen Offset setzen

Ich würde loLetzteZeile und rngLetzteZeile nehmen. lo weist darauf hin, dass es um eine long-Variable geht und bei rng erkennt man den Range. Auch bei anderen Variablen kann man so vorgehen, z.B. wsInhalt als Kennzeichen für ein WorkSheet. Bei den Schleifenzählern würde ich auch mehr als einen Buchstaben nehmen. Wenn Du mal nach dem Zähler i suchst, findest Du sicher nicht nur zutreffendes - nimm z.B. iCnt01, loCnt02 für einen Integer- und einen Longzähler (cnt = Counter)
...

[slowboarder]

kein Fehler:
Hochkommas braucht man nur, wenn die Blattnamen es erfordern.

naja, wenn man das Hochkomma am Anfang setzt, dann muss man auch das zweite am Ende setzen.
Wenn aber nur eines gesetzt ist wie hier im gezeigten Code, dann ist das auf jeden Fall ein Fehler.

und in Anbetracht der Tatsache, dass schon ein normales Leerzeichen die Hochkommas um den Blattnamen erforderlich macht, sollte man diese immer verwenden, vor allem wenn mit Blattnamen gearbeitet wird, die vom Anwender frei vergeben werden.

[Katja213]

Danke euch allen für die Antworten, mit ein bisschen kombinieren der vorgeschlagenen Lösungsmöglichkeiten hab ich jetzt eine schöne Lösung gebastelt.

Falls es jemanden interessiert, hier ist das fertige Makro:

Code:

Sub Inhaltsverzeichnis()

Dim i As Integer
Dim Inhalt As Worksheet

Set Inhalt = Worksheets("Inhalt")

' Bestehenden Inhalt löschen:
Inhalt.Range("2:200").ClearContents

' Für jedes Tabellenblatt durchführen, ab dem zweiten bis zum letzten:
For i = 2 To Worksheets.Count

' Den Bereich definieren, der in das Inhaltsverzeichnis reinkopiert werden soll:
Set BereichZielTab = Worksheets(i).Range("A3,A5,A7,A9")
' Den Bereich im Inhaltsverzeichnis definieren, wo die kopierten Informationen eingefügt werden sollen:
' Es soll unter der letzten Zelle, die etwas enthält, eingefügt werden
Set LetzteZeileInhalt = Worksheets(1).Cells(Rows.Count, "A").End(xlUp)(2)

' Jetzt die definierten Informationen kopieren, Formatierung entfernen und transponiert an der definierten Stelle einfügen
BereichZielTab.Copy
LetzteZeileInhalt.PasteSpecial Paste:=xlPasteValues, Transpose:=True

' Dasselbe Vorgehen auch für das nächste Tabellenblatt durchführen
Next i

' Jetzt Hyperlinks einfügen, mit denen es möglich ist, zwischen den Blättern zu wechseln:
For Each it In Sheets
    n = n + 1
    it.Hyperlinks.Add Cells(n, 1), "", it.Cells(1).Address(, , , True)
  Next

End Sub

Viele Grüße
Katja

[HKindler]

Hi,

bei 70(!!!) Blättern würde ich mir eher Gedanke darum machen, ob das sinnvoll ist. Denk mal über den Beitrag #2 von snb nach.

Da du aus den Blättern für deinen Hyperlink jeweils A1 und für den "Inhalt" A3, A5, A7 und A9 verwendest, vermute ich sehr stark, dass du in "Karteikarten" denkst. Soll heißen: du hast 70 "Produkte", die irgendwelche Eigenschaften haben, die in den Blättern festgehalten sind. Alle Blätter sind gleich aufgebaut und auf "schön" bzw. gut lesbar getrimmt. Am Besten noch mit verbundenen Zellen und farbigen Kennzeichnungen der einzelnen Bereiche.

Hier kommt jetzt der Vorschlag von snb ins Rennen:
Mach ein Blatt (Datenquelle), mit einer einzigen Tabelle. Jede Zeile in der Tabelle hat den Inhalt jeweils eines deiner Blätter. Jede Spalte entspricht einer Eigenschaft des jeweiligen Produkts, also z.B. eindeutige ID, Name, Gewicht, Farbe, Höhe, Breite, Tiefe etc. pp. Damit hast du bereits alles, was du wirklich benötigst.
Mach ein weiteres Blatt, das einem deiner bisherigen Blätter entspricht. Dieses eine Blatt ersetzt deine bisherigen 70 Blätter. Dort machst du in einer Zelle z.B. A1 ein Auswahlfeld, in dem du das Produkt auswählen kannst, das dich gerade interessiert. In irgendeine andere Zelle kommt eine Formel, mit der du die Zeile in deiner Datenquelle suchst, also so etwas wie "=XVERGLEICH(A1;Datenquelle!$A$2:$A$71)". In die Zellen, in denen du bisher deine Eigenschaften stehen hast, also z.B. A3, A4, A7 und A9 kommen Formeln, die per INDEX die jeweils benötigte Eigenschaft aus der Datenquelle holen, also so etwas wie "=INDEX(Datenquelle!$A$2:$XY$71;A1;2)" Hierbei steht in A1 die XVERWEIS-Formel und du willst den Wert aus der zweiten Spalte haben.

Vorteil: du brauchst kein extra Blatt mit Hyperlinks und somit auch keine Makros. Wenn du das Aussehen deiner Blätter ändern willst, dann musst du das nur noch in einem Blatt machen und nicht in 70 Blättern. Hast du generelle Änderungen zu machen, z.B. ein weiteres Merkmal einzuführen, so kannst du das für alle Produkte in einem einzigen Blatt machen und musst noch auf 70 verschiedenen Blättern vorbeischauen.

Denk mal bitte darüber nach. Es würde dir das Leben sicherlich vereinfachen.

[Katja213]

Normalerweise würde ich dir zu 100% zustimmen, und wenn die Datei nur für mich wäre, würde ich sie auch entsprechend aufbauen. Eine Datenbank und ein lesbares Blatt machen das Leben wirklich einfacher. Die Datei muss mich jedoch leider "überleben" und leicht zu verstehen sein, und vor allem leicht zu retten, wenn jemand irgendwo etwas geändert hat, was man nicht hätte ändern sollen. Deshalb sind mir Makros lieber, da passieren Änderungen nicht ganz so schnell wie bei Formeln.
Ich werde mir die Vorschläge trotzdem zu Herzen nehmen und gucken, ob ich es doch noch ohne Makros umgesetzt bekomme. Danke für eure Beiträge!