Problem mit NumberFormat

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: Problem mit NumberFormat
von: Jens_Pu
Geschrieben am: 01.12.2003 19:09:38

Hallo Excelfreunde,

für eine Arbeitszeiterfassung wollte ich die komfortable Eingabe von Zeitwerten.
In der Recherche habe ich was gefunden und an meine Bedürfnisse etwas angepasst.
Allerdings ist da irgendwie der Wurm drin. Bei der Zuweisung des Formats hört die Routine einfach auf. Keine Fehlermeldung, nix. Natürlich ist dann auch immer Application.EnableEvents auf False. Bin für jeden Hinweis dankbar.



Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row < 16 Then Exit Sub
  If Not (Target.Column = 4 Or Target.Column = 5 Or Target.Column = 6 _
          Or Target.Column = 7) Then Exit Sub
  If Target.Value = "" Then Exit Sub
  Application.EnableEvents = False
  If Target.Column = 7 Then Target = UCase(Target)
  With Target
     If IsNumeric(.Value) And InStr(.Value, ":") = 0 And InStr(.Value, ",") = 0 Then
         .NumberFormat = "[hh]:mm"  <-----Hier bricht Excel ab!!!
         Select Case Len(.Value)
             Case 1
                 .Value = "00:0" & .Value
             Case 2
                 .Value = "00:" & .Value
             Case 3
                 .Value = "0" & Left(.Value, 1) & ":" & Right(.Value, 2)
             Case Else
                 .Value = Left(.Value, Len(.Value) - 2) & ":" & Right(.Value, 2)
         End Select
     End If
  End With
  Application.EnableEvents = True
End Sub


Gruss Jens
Bild


Betrifft: AW: Problem mit NumberFormat
von: Ramses
Geschrieben am: 01.12.2003 19:18:58

Hallo Jens

wenn eine richtige Zahl drin steht, dann funktioniert es bei mir.

ein Problem sehe ich,

InStr(.Value, ":") = 0

wird nix richtiges rausbringen, weil du mit ".Value" den reinen Inhalt ( die reine Zahl !! ) und nicht das sichtbare Format prüfst.
Die Prüfung ist insofern also überflüssig.

Warum

InStr(.Value, ",") = 0

Eine Komma-Stelle brauchst du immer um die Zeit darzustellen. Warum also die Prüfung
?

Die andere Prüfung mit Select Case ist mir ebenfalls schleierhaft. Was willst du damit erreichen ?

Gruss Rainer


Bild


Betrifft: AW: Problem mit NumberFormat
von: Jens_Pu
Geschrieben am: 01.12.2003 19:29:18

Hallo Rainer,

hab doch geschrieben, dass das nicht von mir ist.
Ziel sollt es sein aus 7 wird 7:00, aus 701 wird 7:01, aus 1425 wird 14:25 usw.
Zur Prüfung mit InStr(.Value, ":") = 0 würde ich sagen, dass es dazu dienen soll Eingaben von 7:03 abzufangen.
Der Case Konstrukt soll wohl oben geschilderte Eingabemöglichkeiten handeln.

Gruss Jens


Bild


Betrifft: Das geht aber einfacher...
von: Ramses
Geschrieben am: 01.12.2003 19:33:41

Hallo

das geht einfacher

With Target
If IsNumeric(.Value) Then
.Value = .Value/24
.Numberformat = "[hh]:mm"
End If
End With

Gruss Rainer


Bild


Betrifft: AW: Das geht aber einfacher...
von: PeterW
Geschrieben am: 01.12.2003 19:40:20

Hallo Rainer,

wie wird bei deinem Ansatz aus 701 7:01?

Gruß
Peter
PS: sollen wir mal eine Sammelbestellung für Glaskugeln aufgeben? :-)


Bild


Betrifft: Ok, Ok,....
von: Ramses
Geschrieben am: 01.12.2003 19:47:55

Hallo Peter

... die Bestellung können wir schon aufgeben, aber wenn du deine immer bei e-bay versteigerst :-))

Damit geht es aber sicher, den Zusatz mit 1734 habe ich tatsächlich überlesen


Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column <> 7 Or Target.Row < 16 Then Exit Sub
With Target
     If IsNumeric(.Value) Then
         Select Case Len(.Value)
             Case 1
                 .Value = "00:0" & .Value
             Case 2
                 .Value = "00:" & .Value
             Case 3
                 .Value = "0" & Left(.Value, 1) & ":" & Right(.Value, 2)
             Case Else
                 .Value = Left(.Value, Len(.Value) - 2) & ":" & Right(.Value, 2)
         End Select
         .NumberFormat = "[hh]:mm"  '<-----Hier bricht Excel ab!!!
     End If
End With
Application.EnableEvents = True
End Sub


Gruss Rainer


Bild


Betrifft: Excel bricht natürlich nicht ab...
von: Ramses
Geschrieben am: 01.12.2003 19:49:07

....wie im Code angezeigt.
Kopierfehler

Gruss Rainer


Bild


Betrifft: Excel bricht ab!!!! Ohne Meldung!
von: Jens_Pu
Geschrieben am: 01.12.2003 19:53:44

Im Debugger mit F8 Einzelschritt verfolgt.

Deswegen bleibt Application.EnableEvents ja auf FALSE!

Das ist ja das Problem.

Gruss Jens


Bild


Betrifft: Schau bitte in meinen anderen Beitrag. o.T.
von: Ramses
Geschrieben am: 01.12.2003 19:57:43

...


Bild


Betrifft: AW: Ok, Ok,....
von: Jens_Pu
Geschrieben am: 01.12.2003 19:51:11

Hallo Rainer,

das kann von der Theorie schon nicht gehen.
Wie wird Application.EnableEvents wieder True, wenn z.B. Target.Row < 16?

Schau doch mal was das Problem ist, DER ABBRUCH!!

Gruss Jens


Bild


Betrifft: Das problem habe ich nicht :-)
von: Ramses
Geschrieben am: 01.12.2003 19:55:51

Hallo Jens

der Einwand ist OK, lässt sich aber einfach lösen

If Target.Column <> 7 Or Target.Row < 16 Then
Application.EnableEvents = True
Exit Sub
End If

Und das andere Problem kann ich nicht nachvollziehen !!

Siehe beiliegende Mappe
https://www.herber.de/bbs/user/2252.xls

Gruss Rainer


Bild


Betrifft: OT Kugeln
von: PeterW
Geschrieben am: 01.12.2003 20:30:48

Hallo Rainer,

ich versteigere nichts - die Dinger sind eben ziemlich zerbrechlich, und wenn man die massiven nimmt schmerzt es sehr, wenn sie vom Tisch rollen und den Fuß (oder andere Körperteile) treffen.

Gruß
Peter
PS: dass ein Code von dir keinen Abbruch mehr erzeugt dürfte doch klar sein :-))


Bild


Betrifft: AW: OT Kugeln
von: Jens_Pu
Geschrieben am: 01.12.2003 20:35:19

Hallo Peter,

da wäre ich mir noch nicht so sicher.
Ich teste noch.

Gruss Jens


Bild


Betrifft: Nimm das mal....
von: Ramses
Geschrieben am: 01.12.2003 20:36:45

Hallo Jens,

hier mit ein paar zusätzlichen Prüfungen:


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column <> 7 Or Target.Row < 16 Then
    Application.EnableEvents = True
    Exit Sub
End If
With Target
    If IsEmpty(.Value) Then
        Application.EnableEvents = True
        Exit Sub
    End If
    'Entfernen von Kommas aus einer Eingabe
    If InStr(1, .Value, ",") > 0 Then
        .Value = Application.WorksheetFunction.Replace(.Value, InStr(1, .Value, ","), 1, "")
    End If
    If IsNumeric(.Value) Then
        Select Case Len(.Value)
            Case 1
                .Value = "00:0" & .Value
            Case 2
                .Value = "00:" & .Value
            Case 3
                .Value = "0" & Left(.Value, 1) & ":" & Right(.Value, 2)
            Case 4
                .Value = Left(.Value, Len(.Value) - 2) & ":" & Right(.Value, 2)
            Case Else
                'Provisorisches Abfangen einer Zeiteingabe
                'Case 5 geht nicht, da bei Eingabe einer Uhrzeit
                'eine mehrstellige Nachkommazahl entstehen kann
                'Die einfache Prüfung, wenn oben alles durch ist,
                'lautet hier, ob der WErt numerisch ist...
                If IsNumeric(.Value) Then
                    Application.EnableEvents = True
                    Exit Sub
                End If
                '... wenn nicht löschen
                .Value = ""
                MsgBox "Unerlaubte Eingabe"
        End Select
    .NumberFormat = "[hh]:mm"
    End If
End With
Application.EnableEvents = True
End Sub 
     Code eingefügt mit Syntaxhighlighter 1.16



By the way:
Peter hat mehr Vertrauen ;-))

Gruss Rainer


Bild


Betrifft: Problem besteht
von: Jens_Pu
Geschrieben am: 01.12.2003 21:12:55

Hallo Ramses,

ich habe schon Vertrauen in Deine Fähigkeiten ;-)
Habe schon oft was von Dir gelesen und gelernt...

Die Prüfung von Falscheingaben waren auch meine Bedenken, aber da hast Du ja jetzt was eingebaut.

Problem:
Habe mal Deine neueste Variante eingebaut.
Wenn ich z.B. 7 eingebe erscheint 00:07 korrekt.
Wenn ich nun in die gleiche Zelle gehe, wo 00:07 steht und 16 eingebe erscheint 384:00
Ich dachte erst, es liegt daran weil ich 1904-Datumswerte in den Optionen eingestellt habe (wegen negativer Zeitwerte). Das ist aber nicht der Fall.

Ich bin ratlos.

Gruss Jens


Bild


Betrifft: Hier kommt Rat :-)
von: Ramses
Geschrieben am: 01.12.2003 21:20:55

Hallo Jens

das Problem entsteht, weil EXCEL bei einem bestehenden Uhrzeitformat die Eingabe automatisch umwandelt.
Verwende diesen Code, dann wird das Format zurückgesetzt und die Eingabe korrekt erkannt.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column <> 7 Or Target.Row < 16 Then
    Application.EnableEvents = True
    Exit Sub
End If
With Target
    If .NumberFormat = "[hh]:mm" Then
        .NumberFormat = "General"
    End If
    If IsEmpty(.Value) Then
        Application.EnableEvents = True
        Exit Sub
    End If
    'Entfernen von Kommas aus einer Eingabe
    If InStr(1, .Value, ",") > 0 Then
        .Value = Application.WorksheetFunction.Replace(.Value, InStr(1, .Value, ","), 1, "")
    End If
    If IsNumeric(.Value) Then
        Select Case Len(.Value)
            Case 1
                .Value = "00:0" & .Value
            Case 2
                .Value = "00:" & .Value
            Case 3
                .Value = "0" & Left(.Value, 1) & ":" & Right(.Value, 2)
            Case 4
                .Value = Left(.Value, Len(.Value) - 2) & ":" & Right(.Value, 2)
            Case Else
                'Provisorisches Abfangen einer Zeiteingabe
                'Case 5 geht nicht, da bei Eingabe einer Uhrzeit
                'eine mehrstellige Nachkommazahl entstehen kann
                'Die einfache Prüfung, wenn oben alles durch ist,
                'lautet hier, ob der WErt numerisch ist...
                If IsNumeric(.Value) Then
                    Application.EnableEvents = True
                    Exit Sub
                End If
                '... wenn nicht löschen
                .Value = ""
                MsgBox "Unerlaubte Eingabe"
        End Select
    .NumberFormat = "[hh]:mm"
    End If
End With
Application.EnableEvents = True
End Sub
 
     Code eingefügt mit Syntaxhighlighter 1.16



Gruss Rainer


Bild


Betrifft: AW: Hier kommt Rat :-)
von: Jesn_Pu
Geschrieben am: 01.12.2003 21:36:41

Hallo Rainer,

vielen Dank für Deine Geduld.
Prinzipiell funktioniert Deine Routine. Aber nur ein mal!
Wenn ich das mit dem Debugger im Einzelschritt durchgehe, kommt er immer bis zu der Zeile .NumberFormat = "[hh]:mm". Sobald ich in dieser Zeile F8 drücke, verschwindet die gelbe Markierung, aus dem Lautsprecher kommt ein Ping und das wars. Dann verhält sich alles so, wie wenn die Sub beendet wäre. ABER die Application.EnableEvents sind FALSE!

Das ist mir ein Rätsel.

Gruss Jens


Bild


Betrifft: ??
von: Ramses
Geschrieben am: 01.12.2003 21:46:37

Hallo

...wie startest du bei diesem Makro den Einzelschritt Modus ?

Also ich kann dein Problem nicht nachvollziehen.
Bei meiner Datei funktioniert das ohne Probleme auch mehrmals hintereinander

Hier nochmals die korrigierte Datei.

https://www.herber.de/bbs/user/2255.xls


Gruss Rainer


Bild


Betrifft: AW: ??
von: Jens_Pu
Geschrieben am: 01.12.2003 22:12:32

Hallo Rainer,

das verstehe ich nicht. Die Datei von Dir geht.

Ich habe jetzt mal eine neue Tabelle angelegt und meine Daten etc. reinkopiert.
So wie es bisher aussieht, scheint es zu gehen. Vermutlich hat die andere Mappe einen Schaden.
Einen Schönheitsfehler hat Deine super Routine allerdings dennoch.
Wenn man mehrere der eingegebenen zellen markiert und löscht, kommt ein Fehler.
Ist das so i.O.

If IsEmpty(.Value) Or Target.Cells.Count > 1 Then
Application.EnableEvents = True
Exit Sub
End If


Gruss Jens


Bild


Betrifft: Das sollte es nun sein :-)
von: Ramses
Geschrieben am: 01.12.2003 22:26:05

Hallo

probier mal das.
Wenn du noch mehr Kontrollen brauchst, solltest du deine Anwender schulen :-))


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column <> 7 Or Target.Row < 16 Then
    Application.EnableEvents = True
    Exit Sub
End If
If InStr(1, Selection.Address, ":") > 0 Then
    Application.EnableEvents = True
    MsgBox "Sie können Daten löschen, aber keine Daten eingeben bei einer Merhfachselektion"
    Exit Sub
End If
With Target
    If .NumberFormat = "[hh]:mm" Then
        .NumberFormat = "General"
    End If
    If IsEmpty(.Value) Then
        Application.EnableEvents = True
        Exit Sub
    End If
    'Entfernen von Kommas aus einer Eingabe
    If InStr(1, .Value, ",") > 0 Then
        .Value = Application.WorksheetFunction.Replace(.Value, InStr(1, .Value, ","), 1, "")
    End If
    If IsNumeric(.Value) Then
        Select Case Len(.Value)
            Case 1
                .Value = "00:0" & .Value
            Case 2
                .Value = "00:" & .Value
            Case 3
                .Value = "0" & Left(.Value, 1) & ":" & Right(.Value, 2)
            Case 4
                .Value = Left(.Value, Len(.Value) - 2) & ":" & Right(.Value, 2)
            Case Else
                'Provisorisches Abfangen einer Zeiteingabe
                'Case 5 geht nicht, da bei Eingabe einer Uhrzeit
                'eine mehrstellige Nachkommazahl entstehen kann
                'Die einfache Prüfung, wenn oben alles durch ist,
                'lautet hier, ob der WErt numerisch ist...
                If IsNumeric(.Value) Then
                    Application.EnableEvents = True
                    Exit Sub
                End If
                '... wenn nicht löschen
                .Value = ""
                MsgBox "Unerlaubte Eingabe"
        End Select
    .NumberFormat = "[hh]:mm"
    End If
End With
Application.EnableEvents = True
End Sub
 
     Code eingefügt mit Syntaxhighlighter 1.16



Gruss Rainer


Bild


Betrifft: AW: Das sollte es nun sein :-)
von: Jens_Pu
Geschrieben am: 01.12.2003 22:34:36

Hallo Rainer,

habe jetzt auch in der neuen Datei wieder das Problem, dass es nur 1mal läuft.
Kann es aber noch nicht definieren, wie es zu reproduzieren ist. Deine letzte Variante teste ich morgen. Jetzt mache ich für heute Schluss. Habe schon viereckige Augen.

Noch mal vielen Dank.
Vermutlich melde ich mich morgen wieder mit neuen Erkenntnissen.

Gruss Jens


Bild


Betrifft: AW: OT Kugeln
von: Ramses
Geschrieben am: 01.12.2003 20:40:14

Hallo Peter,

dann habe ich dich verwechselt ;-). Sorry.
Ich hatte in einem Beitrag gelesen, dass jemand seine Glaskugel bei e-bay versteigert hätte. Ich dachte das wärest du gewesen.

Gruss Rainer


Bild


Betrifft: AW: Das geht aber einfacher...
von: Jens_Pu
Geschrieben am: 01.12.2003 19:43:50

Hallo Rainer,

habe Deinen Vorschlag mal getestet.
Genau wie in meinem ersten Versuch. Beim ersten Durchlauf bis zum NumberFormat, dann hört Excel einfach auf und kommt nicht mehr in die Zeile mit Application.EnableEvents = True.

So sieht es jetzt aus:


Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row < 16 Then Exit Sub
  If Not (Target.Column = 4 Or Target.Column = 5 Or Target.Column = 6 _
          Or Target.Column = 7) Then Exit Sub
  If Target.Value = "" Then Exit Sub
  Application.EnableEvents = False
  If Target.Column = 7 Then Target = UCase(Target)
  With Target
    If IsNumeric(.Value) Then
      .Value = .Value / 24
      .NumberFormat = "[hh]:mm"  <------ Hier hört Excel einfach auf!!!
    End If
  End With
  Application.EnableEvents = True
End Sub


Gruss Jens


Bild

Beiträge aus den Excel-Beispielen zum Thema " Problem mit NumberFormat"