Blattnamen als Hyperlink einfügen

[ArnoKiel]

Hallo,

ich möchte alle Blattnamen einer Datei als Hyperlink auf einer Übersichtsseite auflisten.

Folgender Code übernimmt mir die Blattnamen, nur mit dem Hyperlink gibts Probleme (Anwendungs- oder objektorientierter Fehler):

Private Sub Worksheet_Activate()
  Range("AA10").Select
  Dim z As Long, s As Long, i As Long
   
  s = Selection.Column
   
  Range("AA10:BL10").Select
  Selection.ClearContents
  Range("AA10").Select
   
  For i = 4 To Sheets.Count - 2
      Cells(10, s) = Sheets(i).Name
      Cells(10, s).Hyperlinks.Add Anchor:=Cells(10, s), Address:="", SubAddress:="'" & Worksheets(i).Name & "!A1", TextToDisplay:=Worksheets(i).Name
      s = s + 2
  Next i

End Sub


Kann  mir jemand helfen?
Danke

[{Boris}]

Hi,

es fehlt nur 1 Hochkomma '

 Cells(10, s).Hyperlinks.Add Anchor:=Cells(10, s), Address:="", SubAddress:="'" & Worksheets(i).Name & "'!A1", TextToDisplay:=Worksheets(i).Name

---

Hi,

alternativ kannst Du das auch mit Formeln dynamisch erledigen.

Dazu musst Du zunächst einen Namen definieren (Strg+F3). Vergib den Namen x
Unter "Bezieht sich auf" gibst Du ein:

=ARBEITSMAPPE.ZUORDNEN(1+0*JETZT())

In Deine Zelle AA10 gibst Du nun diese Formel ein:

=LET(y;INDEX(x;SPALTEN($A:D));WENN(SPALTEN($A:D)>ANZAHL2(x)-2;"";HYPERLINK("#'"&y&"'!A1";TEIL(y;FINDEN("]";y)+1;99))))

und kopierst sie soweit nach rechts, wie es maximal zu verlinkende Blätter geben kann.

Die Formel verlinkt ab dem 4. Blatt bis zum drittletzten Blatt (so geht es zumindest aus Deinem VBA-Code hervor).
Den VBA-Code brauchst Du dann nicht mehr.

[ArnoKiel]

Hallo Boris,

vielen Dank, aber auch mit dem Hochkomma gibt es den gleichen Fehler.

Deine Alternative gibt den Fehler: #Name?

[{Boris}]

Hi,

zeig mal Deine Datei - sowohl mit dem nicht funktionierenden VBA-Code als auch mit dem (nicht funktionierenden) Formelversuch.

[ArnoKiel]

Hallo Boris,

vielen Dank für deine Hilfe. Habe heute aber  leider keine Zeit mehr und bin ab morgen 3 Wochen im Urlaub.

Muss mich dann melden.

[ArnoKiel]

Hallo Boris,

habe heute doch noch Zeit gefunden.

Der Tipp mit dem Hochkomma funktioniert doch (sorry).

Nur wenn ich beim Übersichtsblatt den Blattschutz aktivieren, kommt die Fehlermeldung (Die Zellen AA1 bis BL1 sind nicht gesperrt).
Was mache ich falsch?????

[ArnoKiel]

Hallo Boris,

ich nochmal.

Ich werden einfach den Blattschutz nicht mit Passwort versehen und den Schutz vor einfügen der Blattnamen ausstellen und dan wieder ein.

Vielen Dank für deine Hilfe.

[{Boris}]

Hi,

ich versteh nicht, warum Du nicht die Formelvariante mit dem Namen (x) verwendest. Du hast den Namen ja nicht einmal in der Mappe definiert, was mir zeigt, dass Du es entweder nicht versucht hast oder eben nicht weißt, wie das geht - dann frag doch nach!

Ich hab Dir das mal in die Mappe eingebaut - Dein Makro hab ich komplett entfernt, weil überflüssig. Somit ist auch der Blattschutz jetzt aktiv und nicht mehr im Weg.

Verlinkt werden jetzt alle Blätter außer das erste und das letzte - und das ist dynamisch: Fügst Du neue Blätter ein, werden diese automatisch mit verlinkt.

BTW:

=WENNFEHLER(WENN(SUMME(AA4)+SUMME(AC4)+SUMME(AE4)+SUMME(AG4)+SUMME(AI4)+SUMME(AK4)+SUMME(AM4)+SUMME(AO4)+SUMME(AQ4)+SUMME(AS4)+SUMME(AU4)+SUMME(AW4)+SUMME(AY4)+SUMME(BA4)+SUMME(BC4)+SUMME(BE4)+SUMME(BG4)+SUMME(BI4)+SUMME(BK4)=0;"";SUMME(AA4)+SUMME(AC4)+SUMME(AE4)+SUMME(AG4)+SUMME(AI4)+SUMME(AK4)+SUMME(AM4)+SUMME(AO4)+SUMME(AQ4)+SUMME(AS4)+SUMME(AU4)+SUMME(AW4)+SUMME(AY4)+SUMME(BA4)+SUMME(BC4)+SUMME(BE4)+SUMME(BG4)+SUMME(BI4)+SUMME(BK4));"")

SUMME kann auch mit mehreren Einzelbezügen: =SUMME(AA4;AC4;AE4) - damit wird die Formel viel kürzer.
Und da offensichtlich jede zweite Spalte summiert werden soll, geht es nochmal viel kürzer. Frag nach, wenn Du daran Interesse hast.

Datei wegen der Blatt-Links anbei.

[RPP63]

Moin!
Ich kapere mal den Thread, weil ich auf ein interessantes Problem gestoßen bin.

@{Boris}:
Wenn schon dynamisch, dann aber gleich richtig und vor allem extrem schlank.
Namensvergabe wie von Dir gezeigt;
die horizontale Liste aller Tabellen-Namen erhält man dann mit dieser flauschigen Spill-Formel:

	A	B	C	D	E	F	G
1	Kamelle	Banane	Rhabarber	Tinnitus	Wochenende	Clever-Excel

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

Zelle	Formel
A1	=TEIL(x;FINDEN("]";x)+1;31)

Jetzt dachte ich, mittels
=HYPERLINK("#'"&A1#&"'!A1";A1#)
(könnte man natürlich auch insgesamt in eine LET() packen)
das Problem gelöst zu haben, aber …

	A	B	C	D	E	F
1	Kamelle	Banane	Rhabarber	Tinnitus	Wochenende	Clever-Excel
2	Kamelle	Kamelle	Kamelle	Kamelle	Kamelle	Kamelle

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

Zelle	Formel
A1	=TEIL(x;FINDEN("]";x)+1;31)
A2	=HYPERLINK("#'"&A1#&"'!A1";A1#)

Das seltsame ist, dass die Hyperlinks korrekt funktionieren, lediglich der friendly Name wird falsch angezeigt:
   

Ich hänge die Datei mal an (.xlsm wegen des Excel4-Makros).

Gruß Ralf

[{Boris}]

Hi Ralf,

verschieb die HYPERLINK-Formel mal z.B. in die Zelle C4. Dann verlinkt C4 nach Rhabarber - also den korrespondierenden Wert aus Zeile 1 der selben Spalte. Ab G4 gibt`s dann keinen Link mehr.
Der Friendly-Name bleibt ebenfalls stets bei Eintrag 1 aus A1#.
Stößt des Spillen hier tatsächlich an Grenzen?