Office: (Office 2016) Sonderzeichen finden per VBA

Liebes Forum!

Ich habe ein Problem. Sonderzeichen (SZ) per VBA bei Eingabe überprüfen.

In einem Tabellenblatt (Datei 1) habe ich ein Auswahlfeld (B1) mit Kurzbezeichnungen. Aus dieser Auswahl wird das Tabellenblatt über VBA umbenannt, funktioniert super. Sind in diesem Auswahlfeld Sonderzeichen z.B. / : * ? " < > | erscheint eine MsgBox da das Tabellenblatt diese Zeichen nicht unterstützt.

Datei 1: Code im Tabellenblatt

Private Sub Worksheet_Change(ByVal Target As Range)
'Sonderzeichen suchen, da die Kurzbezeichnung als Tabellenblattname verwendet wird.
If Not Application.Intersect(Target, Range("B1:B1")) Is Nothing Then
On Error GoTo Fehlermeldung
If Target = "" Then Exit Sub
ActiveSheet.Name = Range("B1").Value
End If
Exit Sub

Fehlermeldung:
MsgBox "Ungültiges Zeichen in der Kurzbezeichnung." & vbCrLf & vbCrLf & _
"Da die Kurzbezeichnung als Tabellenblattname verwendet wird, dürfen keine Sonderzeichen verwendet werden." & vbCrLf & vbCrLf & _
"Der Kurzbezeichnung enthält ungültige Zeichen wie * / : ? | [ ]" & vbCrLf & vbCrLf & _
"Bitte Kurzbezeichnung korrigieren. Erst danach kann die Kurzbezeichnung in das Tabellenblatt übernommen werden."

End Sub
_________________
In einer externen Exceldatei (Datei 2) Tabellenblatt habe ich die Auflistung mit Namen und Kurzzeichen (Spalte B2:B80) für die Datei 1.

In Datei 2 bekomme ich schon bei einem Minuszeichen in Spalte B die MsgBox dass dieses Zeichen nicht verwendet werden darf. .

Datei 2: Code im Tabellenblatt
Private Sub Worksheet_Change(ByVal Target As Range)
'Sonderzeichen melden, da die Kurzbezeichnung als Tabellenblattname verwendet wird.

If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then
On Error GoTo fehlermeldung
If Target = "" Then Exit Sub
ActiveSheet.Name = Range("B:B").Value
End If
Exit Sub

fehlermeldung:
MsgBox "Ungültige Zeichen in der Kurzbezeichnung." & vbCrLf & vbCrLf & _
"Da die Kurzbezeichnung als Tabellenblattname verwendet wird, dürfen keine Sonderzeichen wie * / : ? | [ ] verwendet werden." & vbCrLf & vbCrLf & _
"Bitte Kurzbezeichnung korrigieren."

End Sub

In Datei2 folgt schon die Meldung bei einem Minuszeichen.

In beiden Dateien (1+2) sollen in der Kurzbezeichnung keine Sonderzeichen verwendet werden.


Vielen Dank für Eure Unterstützung.

LG Sigi:

:)

 

Code:

Du willst dem Blatt als Namen die Werte aller Zellen der Spalte B geben?

Deine Fehlerroutine gibt bei jedem Fehler undifferenziert die gleiche Meldung aus.

Anstelle den Code blindlings in einen Fehler rammeln zu lassen:
- Prüfe den Wert auf unzulässige Zeichen
- Prüfe, ob ein Blatt gleichen Namens bereits existiert.
- Verwende Me anstelle Activesheet.

 

Hallo EarlFred!

Vielen Dank für deine Antwort. Aber es funktioniert nicht. Es wird noch immer bei einem Minuszeichen eine Fehlermeldung angezeigt.

In der Datei 1 gibt es nur eine Zelle (B1) für die Listenauswahl der Kurzzeichen der Abteilungen. Diese Auswahl wird dann zur Benennung des Tabellenblattes herangezogen. Die Daten für die Auswahlliste befindet sich in Datei 2.

In der Datei 2 befinden sich die Einträge aller Abteilungen (Spalte A = Langbezeichnung), in der Spalte B sind die Kurzzeichen. In diesen sollen sich keine Sonderzeichen befinden.

Für die Prüfung ob ein Blatt gleichen Namens bereits existiert, fehlt mir leider das wissen in VBA. Für eine Lösung wäre ich sehr dankbar.

Vielen Dank.

LG Sigi

 

Hallo,

ich würde es anders rum machen ... siehe hier

Für das Worksheet, such nach 'WorksheetExists'

Sabina

 

Hallo Sabina, leider sind meine VBA Kenntnisse sehr schlecht, und größtenteils noch spanische Dörfer.
LG

 

Hallo,

so ...
Code:

Sabina

 

Hallo Sabina!

Danke für die Rückmeldung. Da ich nicht perfekt bin, dazu meine Fragen zu deinen Ausführungen:

Dieser Code kommt in das Tabellenblatt der Datei 1. -> Korrekt Ja/Nein

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strBezeichnung As String

If Target = "" Then Exit Sub
If Not Application.Intersect(Target, Range("B1")) Is Nothing Then
strBezeichnung = ClearValue(Target.Value)
If Not WorkSheetExists(strBezeichnung) Then
ActiveSheet.Name = strBezeichnung
Else
'*** etwas anderes ***
End If
End If
Exit Sub
End Sub

Diese Codes "Funktionen" kommen in das Arbeitsblatt der Datei 1. -> Korrekt Ja/Nein

Function ClearValue(ByVal sTmp As Variant) As String
Dim i As Long
Dim a As String
Dim sText As String
On Error GoTo weiter

For i = 1 To Len(sTmp)
a = Mid(sTmp, i, 1)
If a Like "[ 0-9A-Za-zÄäÖöÜüß_.]" Then sText = sText & a
weiter:
Next i
ClearValue = sText
End Function

Function WorkSheetExists(ByVal WS As Variant) As Boolean
On Error Resume Next
WorkSheetExists = Not Worksheets(WS) Is Nothing
End Function


In Datei 2 sind alle Abteilungen in Langschrift (Spalte A), Kurzzeichen in Spalte B eingetragen. Aus dieser Datei werden in der Datei 1 in Zelle B1 eine Abteilung ausgewählt, deren Kurzzeichen in B1 eingetragen wird.

Wenn ich nun in der Datei 2 die ganze Spalte B auf Sonderzeichen überprüfen möchte, muss ich alles gleich eintragen wie in der Datei nur Range("B1") ändern auf Range("B:B"). Ist das Korrekt.

Vielen Dank für deine Hilfe.

LG Sigi

 

Hallo,

ich habe noch immer nicht begriffen, wie Datei 1 und Datei 2 zusammenhängen. Deshalb kann ich dir auch nur theoretisch weiterhelfen.
Was willst du mit den 78 Zellen in Spalte B ... wie viele Tabellenblätter willst du umbenennen ? *eek.gif*

Musterdatei mit etwas besserer Erklärung ... bitte.

Sabina

 

Hallo Sabina!

Datei 1 funktioniert und kann als abgeschlossen betrachtet werden. Pro Tabellenblatt wird nur in Zelle B1 eine Abteilung eingegeben.

In diesem Tabellenblatt wird in Zelle B1 eine Abteilung ausgewählt, und dieses Kurzzeichen wird als Tabellenname verwendet. Wenn eine bestehende Abteilung in einem weiteren Tabellenblatt eingegeben wird, wird der Bezeichnung eine (2) angefügt. Das ist OK:

In Datei 2 (Abteilungen) sind alle Abteilungen aufgelistet und dienen für die Auswahl in Datei 1 Zelle B1.

In der Spalte B werden die Kurzzeichen für die Abteilungen eingegeben. Beim Verlassen der Zelle soll geprüft werden, ob Sonderzeichen verwendet wurden, wenn ja sollte eine MsgBox mit Hinweis, dass keine Sonderzeichen verwendet werden dürfen, erscheinen.

Datei Abteilungen habe ich angehängt.

Vielen Dank für deine Bemühungen, wünsche dir noch einen schönen Tag.
Liebe Grüße Sigi

 

Hallo,

ins Codemodul der Tabelle1
Code:

Sabina

 

Hallo Sabina!

Vielen Dank für deine Rückmeldung. Haben deinen Code in die Tabelle 1 kopiert.

Leider kommt bei jedem Verlassen der Zelle in Spalte B der Hinweis "keine Sonderzeichen !!!", egal ob in der Zelle nur Buchstaben eingetragen wurden, oder auch Buchstaben mit Zahlenkombinationen z.B. 5-1; oder IM 1 Abt. 3. Wurde in einer Zelle ein Sonderzeichen korrigiert, kommt auch der Hinweiss "keine Sonderzeichen".


Frage: Bei dem Code

If Not a Like "[ 0 / 9A Za zÄäÖöÜüß_.]" Then --> werden die in den Anführungszeichen angeführten Zeichen für die Überprüfung eingegeben und können diese auch noch durch andere Zeichen ergänzt werden. Auf was muß bei der Ergänzung geachtet werden.

Ein großes Dankeschön an deine Geduld

LG Sigi

 

Hallo,

mein Code funktioniert ... den habe ich getestet.
Die Liste der erlaubten Zeichen ist aus meiner Sicht komplett ... mehr erlaubt dir Excel in einem Tabellennamen nicht, oder hat da nichts verloren.
Wenn du die Liste der erlaubten Zeichen abändern möchtest, kann dir die Online-Hilfe zu LIKE helfen.

Außerdem ist es nicht sehr glücklich, wenn du schon eine Datei hochlädst, dass da keinerlei Musterdaten drin stehen, wie es sein kann und wie die hinterher aussehen sollen.

Ich habe eine funktionierende Lösung erstellt ... bei allem anderen, bin ich deshalb raus.

Sabina

 

Danke Sabina!

 

Liebes Forum!

Code von Sabina in die Code-Tabelle kopiert. Bei Verlassen der Spalte B erscheint immer die Fehlermeldung "keine Sonderzeichen", auch wenn kein Sonderzeichen eingetragen wurde.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Me.Range("B:B")) Is Nothing Then
If Target = "" Or Target.Row = 1 Then Exit Sub
If Not ClearValue(Target.Value) Then
MsgBox "Keine Sonderzeichen !!!"
Target.Select
End If
End If
Exit Sub
End Sub

Function ClearValue(ByVal sTmp As Variant) As Boolean
Dim i As Long
Dim a As String
Dim sText As String

For i = 1 To Len(sTmp)
a = Mid(sTmp, i, 1)
If Not a Like "[ 0-9A-Za-zÄäÖöÜüß_.]" Then
ClearValue = False: Exit Function
End If
Next i
End Function



Bitte um Hilfe. Habe die Datei mit Code im Anhang.

Arbeite mit Excel 2016.

Vielen Dank.

LG Sigi

 

Hallo Sigi,

ja klar, das steht doch auch so im Code!

Überlege mal, was die Funktion ClearValue() zurückgibt und was es bedeutet, wenn in der aufrufenden Prozedur "If Not ClearValue(Target.Value) Then" steht.

Sabina hat dir doch "nur" ein Beispiel gegeben - anpassen an deine Gegebenheiten mußt du das schon selber.

Gruß Ulrich