Live-Forum - Die aktuellen Beiträge
Datum
Titel
13.10.2025 19:31:31
VBA Formel mit Bezug auf Überschrift anstatt Spalte
13.10.2025 15:10:17
Überlappende Zeiträume über mehrere Tabellen mit leeren Zell
13.10.2025 13:34:56
PQ Abfrage mit verschiedenen Werten ausführen + Protokoll
13.10.2025 12:24:02
Zählen in Abhängigkeit einer Jahreszahl
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1476to1480
1476to1480
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Tabellenblattname kürzen & Sonderzeichen ersetzen

Forumthread: Tabellenblattname kürzen & Sonderzeichen ersetzen

Tabellenblattname kürzen & Sonderzeichen ersetzen
03.03.2016 19:43:52
schmeli
Hallo Zusammen,
ich hab mich wieder im VBA Dschungel verirrt und krieg mein Makro nicht hin. Vielleicht kann mir jemand von euch auf die Sprünge helfen.
Also folgendes: Ich habe ein Makro, dass neue Tabellenblätter einfügt und sie benennt entsprechend dem Inhalt einer Zelle. Das ganze läuft für jede Zelle die in Spalte B nicht leer ist.
Jetzt ist es allerdings so, dass dieser Name zum einen länger als 31 Zeichen sein kann und ggf. sogar unzulässige Zeichen enthält. Weiß jemand wie ich den Tabellenblatt Namen nach dem 15 Zeichen abschneiden kann und falls "verbotene Zeichen" enthalten sind diese gegen ein Leerzeichen ersetzten kann?
Habs über eine Activesheet.Name = Left(...;15) versucht, krieg aber den Bezug zum frisch erstellten Tabellenblatt nicht hin.
Und bei der Prüfung nach Sonderzeichen bin ich völlig ratlos, außer dass es eine mit "If" anfängt..
Hier wäre der entsprechende Code Ausschnitt:
For myRow = 4 To myLastRow ' Schleife für alle Zeilen ab zeile 2 bis letzte
If Worksheets(1).Cells(myRow, 7).Value = "yes" Then ' prüfen ob anwendbar
Sheets("blanco").Select
Sheets("blanco").Copy Before:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Cells(myRow, 2)
?
Worksheets(1).Hyperlinks.Add Anchor:=Cells(myRow, 9), Address:="", SubAddress:=ActiveSheet.Name & "!B3", TextToDisplay:="Go to worksheet"
End If
Next myRow
(..)
Vielen lieben Dank für eure Hilfe!

Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Tabellenblattname kürzen & Sonderzeichen erset
03.03.2016 20:15:37
schmeli
Hallo Werner,
vielen lieben Dank! Hab mir den Code kurz angesehen, so wie ich ihn lese funktioniert die Umbenennung immer über eine Msg Box.
Das wollte ich vermeiden.
Mein Code liest erst alle Dateien in einem Verzeichnis aus und trägt sie dann ein. Über einen zweiten Cmd kann dann der Benutzer für "relevante" Dateien ein Tabellenblatt anlegen. Leider kann das in vielen Fällen mehr als 20 Blätter betreffen. Deswegen würde ich gerne gleich automatisch den Blattnamen abschneiden und alle Sonderzeichen gegen ein Leerzeichen ersetzten.
Kennst du hier vll noch eine andere Lösung?
Hab auch schon überlegt eine Prüfung einzubauen, wenn in ActiveSheet.Name ein "/" enthalten ist dann ersetze oder so... Kriegs aber nicht zusammen gebastelt. So tief steck ich dann leider doch noch nicht in VBA drin.
Gruß schmeli

Anzeige
AW: Tabellenblattname kürzen & Sonderzeichen erset
03.03.2016 20:29:54
Werner
Hallo,
da muss ich leider passen. Ich stell den Beitrag wieder auf offen.
Gruß Werner

AW: Tabellenblattname kürzen & Sonderzeichen ersetzen
03.03.2016 20:45:57
Uduuh
Hallo, 
Function LegalSheetName(strName As String) As String
Dim arrNotAllowed As Variant
Dim n As Integer
'Im Tabellennamen nicht zulässige Zeichen
arrNotAllowed = Array(":", "\", "/", "?", "*", "[", "]")
'unerlaubte Zeichen durch "" ersetzen
For n = 0 To UBound(arrNotAllowed)
strName = Replace(strName, arrNotAllowed(n), "")
Next
'Namen auf 31 Zeichen begrenzen
LegalSheetName = Left(strName, 31)
End Function

ActiveSheet.Name = LegalSheetName(Cells(myRow, 2))

Gruß aus’m Pott
Udo

Anzeige
AW: Tabellenblattname kürzen & Sonderzeichen ersetzen
03.03.2016 21:07:09
KlausF
Hallo Werner,
mit folgender Funktion werden die „verbotenen“ Zeichen aus dem Sheetnamen
durch ein Blanc ersetzt:
Function fncCheckSheetName(strBlatt As String)
'unzulässige Sonderzeichen im vorgesehenen Namen durch " " ersetzen
Dim intPos As Integer, strErgebnis As String
fncCheckSheetName = Left(strBlatt, 15)
For intPos = 1 To Len(fncCheckSheetName)
Select Case Mid(fncCheckSheetName, intPos, 1)
Case ":", "/", "\", "*", "?", "[", "]"
strErgebnis = strErgebnis & " "
Case Else
strErgebnis = strErgebnis & Mid(fncCheckSheetName, intPos, 1)
End Select
Next
fncCheckSheetName = strErgebnis
End Function
Das könnte dann in etwa so in Deinen Codeausschnitt eingebaut werden:
Dim mySheetName As string
.
.
.
.
For myRow = 4 To myLastRow ' Schleife für alle Zeilen ab zeile 2 bis letzte
If Worksheets(1).Cells(myRow, 7).Value = "yes" Then ' prüfen ob anwendbar
Sheets("blanco").Select
Sheets("blanco").Copy Before:=Worksheets(Worksheets.Count)
mySheetName = Cells(myRow, 2)
mySheetName = fncCheckSheetName(mySheetName) ' Sonderzeichen ersetzen und auf 15 Zeichen begrenzen
ActiveSheet.Name = mySheetName
Gruß
Klaus

Anzeige
Tabellenblattname kürzen & Sonderzeichen ersetzen
06.03.2016 15:17:31
schmeli
Hallo zusammen,
hab den Code von Uduuh jetzt verwendet und er funktioniert einwandfrei!
Hab lediglich in der Zeile
strName = Replace(strName, arrNotAllowed(n), "")
"" gegen " " ausgetauscht
Vielen lieben Dank für eure Hilfe!

AW: Tabellenblattname prüfen, ob vorhande
08.03.2016 12:56:19
schmeli
Hallo Zusammen,
jetzt hänge ich wieder im selben Code fest. Was ich natürlich nicht bedacht habe ist, dass das Blatt vielleicht schon vorhanden ist.
Wie kann ich denn jetzt prüfen, ob das Blatt schon da ist, wenn ich die LegalSheetName Function habe.
Macht es mehr Sinn, den Namen in einer Private Function zu prüfen oder innerhalb der Privat Sub?
Hab mal folgende Schleife vorher eingebaut, aber das "LegalSheetName" funktioniert natürlich nicht, aber wie dann?

For i = 1 To Worksheets.Count
If Worksheets(i).Name  LegalSheetName Then
(...)
End if
Next i
Vielen Dank für eure Hilfe!

Anzeige
AW: Tabellenblattname prüfen, ob vorhande
08.03.2016 13:03:26
Daniel
Hi
ob das Blatt schon vorhanden ist, kannst du so prüfen:
dim wshCheck as worksheet
set wshCheck = nothing
On Error Resume Next
set wshCheck = "Tabellenblattname"
On Error Goto 0
if wshCheck is nothing then
Msgbox "Blatt ""Tabellenblattname"" ist nicht vorhanden."
Else
Msgbox "Blatt ""Tabellenblattname"" ist bereits vorhanden."
End If

ob du das jetzt direkt in den Code programmierst oder ob du das in eine Funktion auslagerst, ist dir überlassen.
Ich würde das davon abhängig machen, wie oft du das im Code brauchst.
Gruß Daniel

Anzeige
AW: Tabellenblattname prüfen, ob vorhande
08.03.2016 13:16:37
schmeli
Hallo Daniel,
so was ähnliches habe ich mir auch schon angeschaut, hänge aber daran, dass der Blattname immer anders ist und sich erst in der Prozedur entwickelt.
Hier mal der ganze Code ausschnitt, vll hilft das beim Verständnis:

Private Sub cmd_Sort_Click()
Dim myRow As Long
Dim myLastRow As Long
Dim mySheetName As String
Application.ScreenUpdating = False
With Worksheets(1)
myLastRow = .Cells(Rows.Count, 2).End(xlUp).Row        ' sucht die letzte gefüllte Zelle  _
in Spalte B
If myLastRow

Anzeige
AW: Tabellenblattname prüfen, ob vorhande
08.03.2016 13:22:56
Daniel
Hi
du kannst den Code doch entsprechend einbauen und statt meinem "Tabellenblattname" dann die entsprechende Variable, die den Blattnamen den du verwenden möchstest enthält, einsetzen.
das Prinzip ist ja einfach:
1. die Fehlerbehandlung so einstellen, dass bei einem Fehler mit dem nächsten programmschritt weiter gemacht wird.
2. das Blatt mit dem gewünschten Namen einer Variable zuweisen
3. Überprüfen, ob die Variablenzuweisung erfolgreich war oder nicht.
Wenn du das Prinzip verstanden hast, sollte es dir nicht schwer fallen, dass in deinen Code einzubauen.
Gruß Daniel

Anzeige
AW: Tabellenblattname prüfen, ob vorhande
08.03.2016 20:57:16
schmeli
Hallo Daniel,
vielen Dank für die Antwort, aber genau da liegt mein Problem. Ich weiß, nicht wie ich die Variable auf LegalSheetName gesetzt bekomme.
;
Anzeige
Anzeige

Infobox / Tutorial

Tabellenblattnamen kürzen und Sonderzeichen ersetzen

Schritt-für-Schritt-Anleitung

Um in Excel Tabellenblattnamen zu kürzen und unzulässige Sonderzeichen zu entfernen, kannst Du eine VBA-Funktion verwenden. Hier ist eine Schritt-für-Schritt-Anleitung, wie Du das umsetzt:

  1. Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.

  2. Füge ein neues Modul hinzu: Rechtsklick auf "VBAProject (DeinWorkbook)", dann Einfügen > Modul.

  3. Kopiere und füge folgenden Code in das Modul ein:

    Function LegalSheetName(strName As String) As String
    Dim arrNotAllowed As Variant
    Dim n As Integer
    ' Im Tabellennamen nicht zulässige Zeichen
    arrNotAllowed = Array(":", "\", "/", "?", "*", "[", "]")
    ' unerlaubte Zeichen durch "" ersetzen
    For n = 0 To UBound(arrNotAllowed)
        strName = Replace(strName, arrNotAllowed(n), "")
    Next
    ' Namen auf 31 Zeichen begrenzen
    LegalSheetName = Left(strName, 31)
End Function

  4. Verwende dann die Funktion in Deinem Hauptcode, um den Namen des aktiven Blattes zu setzen:

    ActiveSheet.Name = LegalSheetName(Cells(myRow, 2))

  5. Füge eine Schleife ein, um alle relevanten Blätter zu durchlaufen und den Namen anzupassen.

Häufige Fehler und Lösungen

  • Fehler: "Das Blatt kann nicht umbenannt werden"

    • Lösung: Überprüfe, ob der neue Name bereits existiert. Verwende eine Fehlerbehandlung, um sicherzustellen, dass Du nicht versuchst, ein bestehendes Blatt umzubenennen.

  • Fehler: Ungültige Zeichen im Blattnamen

    • Lösung: Stelle sicher, dass die Funktion LegalSheetName alle unerlaubten Zeichen ersetzt. Du kannst auch eine zusätzliche Prüfung einbauen, die sicherstellt, dass der Name nur gültige Zeichen enthält.

Alternative Methoden

Eine weitere Methode, um unzulässige Sonderzeichen zu entfernen und den Namen zu kürzen, ist die Verwendung einer benutzerdefinierten Funktion. Hier ist ein Beispiel:

Function fncCheckSheetName(strBlatt As String)
    Dim intPos As Integer, strErgebnis As String
    strBlatt = Left(strBlatt, 15)
    For intPos = 1 To Len(strBlatt)
        Select Case Mid(strBlatt, intPos, 1)
            Case ":", "/", "\", "*", "?", "[", "]"
                strErgebnis = strErgebnis & " "
            Case Else
                strErgebnis = strErgebnis & Mid(strBlatt, intPos, 1)
        End Select
    Next
    fncCheckSheetName = strErgebnis
End Function

Diese Funktion ersetzt unerlaubte Zeichen durch ein Leerzeichen und begrenzt den Namen auf 15 Zeichen.

Praktische Beispiele

Hier ist ein praktisches Beispiel zur Verwendung des Codes:

For myRow = 4 To myLastRow
    If Worksheets(1).Cells(myRow, 7).Value = "yes" Then
        Sheets("blanco").Copy Before:=Worksheets(Worksheets.Count)
        Dim mySheetName As String
        mySheetName = Cells(myRow, 2)
        mySheetName = LegalSheetName(mySheetName)
        ActiveSheet.Name = mySheetName
    End If
Next myRow

In diesem Beispiel wird für jede Zeile in Spalte B, die "yes" enthält, ein neues Blatt erstellt und der Name entsprechend angepasst.

Tipps für Profis

  • Nutze die Replace-Funktion effizient, um mehrere unerlaubte Zeichen in einem Schritt zu ersetzen.
  • Implementiere eine Fehlerbehandlung in Deinem Code, um unerwartete Abstürze zu vermeiden.
  • Berücksichtige, dass die maximale Länge eines Blattnamens 31 Zeichen beträgt, und passe Deine Logik entsprechend an.

FAQ: Häufige Fragen

1. Wie kann ich mehr als 31 Zeichen in einem Blattnamen verwenden?

  • Das ist nicht möglich, da Excel eine maximale Länge von 31 Zeichen für Blattnamen hat. Du musst den Namen kürzen.

2. Was sind die unzulässigen Sonderzeichen für Excel-Blattnamen?

  • Unzulässige Zeichen sind: :, \, /, ?, *, [, ]. Diese dürfen nicht im Blattnamen enthalten sein.

3. Wie kann ich überprüfen, ob ein Blatt bereits existiert?

  • Du kannst eine Schleife verwenden, um alle Blätter durchzugehen und zu prüfen, ob der gewünschte Name bereits existiert. Eine Fehlerbehandlung kann ebenfalls genutzt werden, um dies zu überprüfen.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige