PDA

Vollständige Version anzeigen : Eingabe in Zellen mit "Blank" verhindern


rooki_1
11.07.2012, 08:12
Hy,

ich habe eine Tabelle in der ab Spalte L14 - L20000 Teilenummern eingegeben werden.
Kommt eine Nummer doppelt vor werden diese über die Bedingte Formatierung rot eingefärbt.
funktioniert eigentlich super, neulich hat dann ein Kollege eine Zahl doppelt eingegeben, bei dieser Zahl hatte er hinten dran noch ein Leerzeichen eingegeben. Somit wird diese dann nicht mehr als "doppelt" erkannt.

Kann man das irgendwie umgehen.???

Danke Rooki

hoerzt
11.07.2012, 09:25
Hallo Rooki,

hier könnte dir m.E. die Funktion GLÄTTEN() weiterhelfen.

LG hoerzt

ASE
11.07.2012, 09:33
Hallo Rooki,
die Function "Glätten" oder "Säubern" nützt natürlich nur etwas wenn Du eine weitere Spalte für die Eingaben vorsiehst.
Die elegantere Variante wäre Du schreibst im Codebereich der Tabelle(!) diesen Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = Trim(Target.Value)
End Sub

rooki_1
11.07.2012, 09:36
Hi ASE,

das bezieht sich dann aber auf das gesamte Blatt oder ? Es sollte eigentlich nur für Spalte "L" 14 bis 20000 gelten.

Muss man das noch konkretisieren ?

Gruß Rooki

ASE
11.07.2012, 09:56
Hallo Rooki,
so:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 13 And Target.Row < 20001 And Target.Column = 12 Then
Target.Value = Trim(Target.Value)
End If
End Sub

rooki_1
11.07.2012, 10:06
Hi,

ich habe schon einen Code mit "Private Sub Worksheet_Change" kann man ds elegant verheiraten.??


Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="ludolf" 'Blattschutz wird aufgehoben
'Das Makro startet automatisch, wenn der Inhalt einer Zelle verändert wird.
'(Gilt nicht, wenn sich nur das Ergebnis einer Formel ändert !!! )
'Die veränderte(n) Zelle(n) werden automatisch in der Variablen Target
'an das Makro übergeben.

Application.EnableEvents = False 'Bei Änderung in einer Zelle, wird der Benutername, Zeit und Datum in die Zellen AH,AI und AJ geschrieben
Cells(Target.Row, "AH") = Date 'Über die bedingte Formatierung wird die Zelle K entsprechend eingefärbt
Cells(Target.Row, "AI") = Time
Cells(Target.Row, "AJ") = Environ("Username")
Application.EnableEvents = True

Dim rng As Range

'Nichts machen, wenn Zellen in mehreren Spalten gleichzeitig verändert werden.
If Target.Columns.Count > 1 Then Exit Sub
'Nichts machen, wenn die Änderung bis einschl. Zeile 1 stattfindet
If Target.Row < 2 Then Exit Sub

'Wenn Änderungen in den beiden Formelspalten (X und Y --> Spalten 24 u. 25)
If Target.Column = 24 Or Target.Column = 25 Then
'Schleife über alle Zellen in Target (also alle veränderten Zellen)
For Each rng In Target
'Formel ohne führendes Gleichheitszeichen nach 4 Spalten weiter rechts schreiben
rng.Offset(0, 4) = Right(rng.FormulaLocal, Len(rng.FormulaLocal) - 1)
Next 'rng
'Wenn Änderungen in der Pulsbetrieb/inaktiv/abgeregelt-Spalte (Spalte S --> Spalte 19)
ElseIf Target.Column = 19 Then
'Schleife über alle Zellen in Target (also alle veränderten Zellen)
For Each rng In Target
'Wenn "Pulsbetrieb" oder "Abgeregelt" eingetragen
If rng = "Pulsbetrieb" Or rng = "Abgeregelt" Then
'Ereignisse abschalten, damit sich das Makro nicht selbst rekursiv aufruft
Application.EnableEvents = False
'Gerettete Formel aus Spalte AB (9 Spalten rechts von der "Auswahl"-Spalte)
'in Spalte X (5 Spalten rechts von der "Auswahl"-Spalte) zurückschreiben
rng.Offset(0, 5).FormulaLocal = "=" & rng.Offset(0, 9)
'Spalte Y nur bei Eintrag "Pulsbetrieb" neu berechnen
If rng = "Pulsbetrieb" Then
'Gerettete Formel aus Spalte AC (10 Spalten rechts von der "Auswahl"-Spalte)
'in Spalte Y (6 Spalten rechts von der "Auswahl"-Spalte) zurückschreiben
rng.Offset(0, 6).FormulaLocal = "=" & rng.Offset(0, 10)
Else
'Bei Eintrag "Abgeregelt" Formel der Spalte Y in Text umwandeln
rng.Offset(0, 6) = rng.Offset(0, 6)
End If
'Ereignisse wieder einschalten
Application.EnableEvents = True
Else
'Ereignisse abschalten, damit sich das Makro nicht selbst rekursiv aufruft
Application.EnableEvents = False
'Formeln der beiden Formelspalten (5 bzw. 6 Spalten rechts von der
'"Auswahl"-Spalte) in Werte umwandeln
rng.Offset(0, 5) = rng.Offset(0, 5)
rng.Offset(0, 6) = rng.Offset(0, 6)
'Ereignisse wieder einschalten
Application.EnableEvents = True
End If
Next 'rng
End If
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True _
, AllowFiltering:=True, Password:="ludolf" ' Blattschutz wird gesetzt

End Sub

Gruß Rooki

rooki_1
11.07.2012, 10:32
Hi ASE,

hab es hinbekommen, es funktioniert eigentlich auch. Danke nochmal.
Nachteil: bei jeder eingabe einer Nummer habe ich für ca. 30sec die Sanduhr auf dem Bildschirm.

Gruß Rooki
Das wäre so nicht praktikabel.

ASE
11.07.2012, 11:10
Hi,
versuch es mal damit :
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
ActiveSheet.Unprotect Password:="ludolf" 'Blattschutz wird aufgehoben
'Das Makro startet automatisch, wenn der Inhalt einer Zelle verändert wird.
'(Gilt nicht, wenn sich nur das Ergebnis einer Formel ändert !!! )
'Die veränderte(n) Zelle(n) werden automatisch in der Variablen Target
'an das Makro übergeben.

Application.EnableEvents = False 'Bei Änderung in einer Zelle, wird der Benutername, Zeit und Datum in die Zellen AH,AI und AJ geschrieben
Cells(Target.Row, "AH") = Date 'Über die bedingte Formatierung wird die Zelle K entsprechend eingefärbt
Cells(Target.Row, "AI") = Time
Cells(Target.Row, "AJ") = Environ("Username")


'Nichts machen, wenn Zellen in mehreren Spalten gleichzeitig verändert werden.
If Target.Columns.Count > 1 Then Exit Sub
'Nichts machen, wenn die Änderung bis einschl. Zeile 1 stattfindet
If Target.Row < 2 Then Exit Sub
If Target.Row > 13 And Target.Row < 20001 And Target.Column = 12 Then
Target.Value = Trim(Target.Value)
End If
'Wenn Änderungen in den beiden Formelspalten (X und Y --> Spalten 24 u. 25)
If Target.Column = 24 Or Target.Column = 25 Then
'Schleife über alle Zellen in Target (also alle veränderten Zellen)
For Each rng In Target
'Formel ohne führendes Gleichheitszeichen nach 4 Spalten weiter rechts schreiben
rng.Offset(0, 4) = Right(rng.FormulaLocal, Len(rng.FormulaLocal) - 1)
Next 'rng
'Wenn Änderungen in der Pulsbetrieb/inaktiv/abgeregelt-Spalte (Spalte S --> Spalte 19)
ElseIf Target.Column = 19 Then
'Schleife über alle Zellen in Target (also alle veränderten Zellen)
For Each rng In Target
'Wenn "Pulsbetrieb" oder "Abgeregelt" eingetragen
If rng = "Pulsbetrieb" Or rng = "Abgeregelt" Then
'Ereignisse abschalten, damit sich das Makro nicht selbst rekursiv aufruft
'Gerettete Formel aus Spalte AB (9 Spalten rechts von der "Auswahl"-Spalte)
'in Spalte X (5 Spalten rechts von der "Auswahl"-Spalte) zurückschreiben
rng.Offset(0, 5).FormulaLocal = "=" & rng.Offset(0, 9)
'Spalte Y nur bei Eintrag "Pulsbetrieb" neu berechnen
If rng = "Pulsbetrieb" Then
'Gerettete Formel aus Spalte AC (10 Spalten rechts von der "Auswahl"-Spalte)
'in Spalte Y (6 Spalten rechts von der "Auswahl"-Spalte) zurückschreiben
rng.Offset(0, 6).FormulaLocal = "=" & rng.Offset(0, 10)
Else
'Bei Eintrag "Abgeregelt" Formel der Spalte Y in Text umwandeln
rng.Offset(0, 6) = rng.Offset(0, 6)
End If
'Ereignisse wieder einschalten
Else
'Ereignisse abschalten, damit sich das Makro nicht selbst rekursiv aufruft
'Formeln der beiden Formelspalten (5 bzw. 6 Spalten rechts von der
'"Auswahl"-Spalte) in Werte umwandeln
rng.Offset(0, 5) = rng.Offset(0, 5)
rng.Offset(0, 6) = rng.Offset(0, 6)
'Ereignisse wieder einschalten

End If
Next 'rng

End If
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True _
, AllowFiltering:=True, Password:="ludolf" ' Blattschutz wird gesetzt
Application.EnableEvents = True
End Sub


Du darft nicht ständig Application.EnableEvents ein und auschalten nur einmal am Anfang und Ende!
So werden ja bei jeder Eintragung/Änderung ein neuer Event ausgelöst!!
Dann dauert es natürlich ewig bis er 10000 mal durchgelaufen ist.

rooki_1
11.07.2012, 11:40
Hi,

das funktioniert zwar, wenn ich aber z.B. 8 mal die gleiche Nummer eingebe (mit Blank) wird das gerade gezogen und rot angezeigt. Lösche ich dann allerdings die Inhalte der eingegebenen Zellen kommt Laufzeitfehler '13' und mein Code bleibt stehen.
Erst nach neustart von Excel läuft das wieder.

Gruß Rooki

ASE
11.07.2012, 11:56
Hi Rooki,
schreib mal bitte folgende Anweisung vor der Zeile:
......
Target.Value = Trim(Target.Value)
...........

also so:

On Error Resume Next
Target.Value = Trim(Target.Value)

rooki_1
11.07.2012, 12:32
Hi ASE,

super das haut hin. Funktioniert tadellos. Könntest du mir Trotzdem noch die Erklärungen für die Zeilen liefern.

If Target.Row < 2 Then Exit Sub
If Target.Row > 13 And Target.Row < 20001 And Target.Column = 12 Then
On Error Resume Next
Target.Value = Trim(Target.Value)
End If

Ich hab es immer gern wenn man mal nachschauen kann was der Code so macht.

Gruß
Rooki

ASE
11.07.2012, 13:41
Hallo Rooki,
' ist aktuelle Zeile größer 13 und kleiner 20001 und ist die Zelle in Spalte 12 (L)
If Target.Row > 13 And Target.Row < 20001 And Target.Column = 12 Then

'für den Fall das ein Fehler auftritt beim Leerzeichen entfernen -tritt auf wenn mehr als eine Zelle selctiert sind und gelöscht wird.
On Error Resume Next

'Rekursive Zuweisung des Zelleninhaltes aber ohne Leerzeichen am Anfang+Ende
Target.Value = Trim(Target.Value)
End If

rooki_1
11.07.2012, 14:08
Hi ASE,

also nochmal ein fettes Danke.

Gruß
Rooki

rooki_1
30.07.2012, 06:11
Hy,

ich habe jetzt doch ein Problem mit dem Code.

Er funktioniert eigentlich schon, nur habe ich die Spalte "L" mit einer bedingten Formatierung versehen das bei doppelter Nummernvergabe die Zellen rot werden.

=ZÄHLENWENN($L$14:$L$20000;L92)>1

In Verbindung mit dem Code

If Target.Row < 2 Then Exit Sub
If Target.Row > 13 And Target.Row < 20001 And Target.Column = 12 Then 'Ist aktuelle Zeile größer 13 und kleiner 20001 und ist die Zelle in Spalte 12 (L)
On Error Resume Next 'Für den Fall das ein Fehler auftritt beim Leerzeichen entfernen -tritt auf wenn mehr als eine Zelle selctiert sind und gelöscht wird.
Target.Value = Trim(Target.Value) 'Rekursive Zuweisung des Zelleninhaltes aber ohne Leerzeichen am Anfang+Ende
End If

bleibt aber die Zelle bei doppelter Nummer Rot, auch nach löschen der doppelten Nummer.
Ohne den Code passiert das nicht, weiß jemand Rat ?

Gruß
Rooki

Hasso
30.07.2012, 09:07
Hallo Rooki,

ich habe eine Tabelle in der ab Spalte L14 - L20000 Teilenummern eingegeben werden.
Kommt eine Nummer doppelt vor werden diese über die Bedingte Formatierung rot eingefärbt.
funktioniert eigentlich super, neulich hat dann ein Kollege eine Zahl doppelt eingegeben, bei dieser Zahl hatte er hinten dran noch ein Leerzeichen eingegeben. Somit wird diese dann nicht mehr als "doppelt" erkannt.

Kann man das irgendwie umgehen.???


Wenn es sich um reine Teilenummern handelt, kannst du Eingabe eines Leerzeichens verhindern, indem du die Datengültigkeit auf Ganze Zahl einstellst.

rooki_1
30.07.2012, 09:11
Hallo Hasso,

leider sind es spezielle Nummern wie 01/40-12 ---> Laufende Nr./Kalenderwoche/Jahr

Gruß
Rooki

Hasso
30.07.2012, 10:18
Hallo rooki,

leider sind es spezielle Nummern wie 01/40-12 ---> Laufende Nr./Kalenderwoche/Jahr

Wenn die Nummer immer den gleichen Aufbau hat, würde ich die entsprechenden Zellen mit einem benutzerdefinierten Zahlenformat versehen.
In deinem Fall wäre das 00"/"00"-"00. Derjenige, der die Zahl einträgt, gibt z.B. 014012 ein und in der Zelle steht dann 01/40-12. Das erleichtert den Anwendern auch die Tipparbeit und schließt Fehler in der Teilenummer größtenteils aus.

rooki_1
30.07.2012, 11:19
Hy Hasso,

komisch aber das funktioniert. Ist erstmal echt ne erleichterung weil man sich Tipparbeit spart, und obwohl ich den Code mit dem "Löschen der Blanks" noch aktiv hab funktioniert es.
Vorher war es so, daß beim Löschen einer Nummer die Zelle Rot wurde, obwohl ja gar nichts mehr drin stand.

Ich werds aber nochmal ausgiebig testen.....

Danke dafür

Gruß Rooki

rooki_1
05.10.2012, 11:38
Hy,

ich brauche doch nochmal Hilfe, folgendes ist mir aufgefallen:

die Formel 00"/"00"-"00 unter Benutzerdefiniert macht aus 010112

die Nr. 01/01-12

Soweit ist das ja gut. Wenn jetzt jemand die gleiche Nr. MIT Sonderzeichen einträgt, wird die Nr. nicht als doppelt erkannt und somit auch nicht Rot.

Das ist schlecht, weil ich mir nie sicher sein kann wer die Nummer "verkürzt" oder mit Sonderzeichen eingibt.

Gibt es da einen Ansatz ???

Grüße Rooki

Hasso
05.10.2012, 12:05
Hallo Rooki,

dann gebe doch für die Zellen, in die die Werte eingetragen werden sollen, bei der Datenüberprüfung "Ganze Zahl ein" - dann kann die Zahl nicht mehr mit Sonderzeichen eingegeben werden. Die Benutzer sollte man natürlich in einer benutzerdefinierten Fehlermeldung darauf hinweisen.

Beispielmappe anbei.

rooki_1
05.10.2012, 12:37
Hallo Hasso,

danke erstmal, das heisst aber jede Menge Tipparbeit, es existieren bestimmt schon 1000 Nummern mit Sonderzeichen.

Zweiter Punkt wäre, wenn wir Teile haben, die zusammengehören dann haben wir diese bisher immer zu einem "Satz" zusammengefasst. z.B
Satz Blabla 01...09/20/12
Das wäre ja dann auch nicht mehr möglich bzw. wir müssten wirklich alle "Sätze"
zu Einzelteilen aufdröseln.

Arbeit Arbeit......


Grüße Rooki

Hasso
05.10.2012, 14:06
Hallo Rooki,

das heisst aber jede Menge Tipparbeit, es existieren bestimmt schon 1000 Nummern mit Sonderzeichen

Das kannst du doch mit Suchen und ersetzen lösen.

Zweiter Punkt wäre, wenn wir Teile haben, die zusammengehören dann haben wir diese bisher immer zu einem "Satz" zusammengefasst. z.B
Satz Blabla 01...09/20/12
Das wäre ja dann auch nicht mehr möglich bzw. wir müssten wirklich alle "Sätze"
zu Einzelteilen aufdröseln.

Darunter kann ich mir nicht so viel vorstellen.

Das Hochladen einer Beispieldatei wäre vielleicht ganz sinnvoll.

rooki_1
05.10.2012, 14:17
Hi Hasso,

die Tabelle ist leider Riesengroß und da einiges an Formeln bzw. VBA vorhanden ist auch leider nicht so einfach zu löschen ohne dass da gar nichts mehr geht.

das mit den Sätzen ist folgender Masen.

anstatt teile die eigentlich zusammen gehören wie zB ein Satz Räder, werden diese nicht Nummeriert mit:
01/39-12
02/39-12
03/39-12
04/39-12

sondern zu einem "Satz" zusammengefasst. Eben 01...04/39-12

Ich hoffe es ist verständlicher geworden. Auch bei den Sätzen ist es immer eine Einheitliche Schreibweise.

"Laufende Nummer" "drei Punkte" "Laufende Nummer" / "Woche" - "Jahr"

Gruß
Rooki

Hasso
08.10.2012, 09:38
Hallo Rooki,

dein Problem hat mich einiges Gehirnschmalz gekostet, aber ich glaube jetzt funktioniert es. Ein Problem war, dass Excel je nach Konfiguration die "..." durch das Zeichen CHR(133) ersetzt, das auch wie drei Punkte aussieht, aber nur ein Zeichen darstellt. Gib mal zum Testen auf dem Ziffernfeld bei gedrückter Alt-Taste 0133 ein, dann siehst du, was ich meine.

Hier der Code:
Option Explicit

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

zahlenformat = "00""/""00""-""00"

'Nur auf Änderungen in Spalte A reagieren und nur wenn einzelne Zelle geändert wurde
If Target.Column <> 1 Or Target.Rows.Count > 1 Then
Exit Sub
Else
Application.EnableEvents = False
On Error GoTo Fehler
Target.NumberFormat = "@"
If InStr(Target, ".") = 0 And InStr(Target, Chr(133)) = 0 Then
Target.NumberFormat = zahlenformat
Else
Target = Replace(Target, "/", "")
Target = Replace(Target, "-", "")
Target = Replace(Target, ".", "")
Target = Replace(Target, Chr(133), "")
Target = Left(Target, 2) & "..." & Mid(Target, 3, 2) & "/" & Mid(Target, 5, 2) & "-" & Right(Target, 2)
End If
End If
Fehler:
Application.EnableEvents = True
End Sub


Beispielmappe wie üblich als Anhang.