finding the blink cause

[Ofer]

Hi,
I have written the following code to add time without semicolon to my
time sheet
all works well but I have this annoying blinking when i'm
deleting/resetting a cell after typing
time in it.

Private Sub Worksheet_Change(ByVal Target As Range)
If ((Target.Column = 2) Or (Target.Column = 3)) And ((Target.Row >=
5) And (Target.Row <= 35)) Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Target = Target / 2400
Target.NumberFormat = "h:mm"
Application.EnableEvents = True
If ((Target = "00:00:00") Or (Target = "00:00") Or (Target
= "0")) Then
Target.NumberFormat = "General"
Application.EnableEvents = True
Target = ""
End If
End If
End If
End Sub

can someone point out the annoying blink cause?

Thanks

 

[KC Rippstein]

Have you tried inserting
Application.ScreenUpdating = False
at the beginning of the macro and
Application.ScreenUpdating = True
just before the End Sub line?

 

[Ofer]

Tried it,
... not working it's like the whole worksheet is blinking

 

[Jason Lepack]

You were changing the target to "" after you made enableevents true so
it was calling itself all over again and strangely enough, "" is
numeric. Why it didn't loop infinitely, I don't know, but it loops
quite a bit.

I changed your code so that it doesn't blink, but it doesn't do what
you said it would. It didn't work right for me the way I downloaded
it. (On further review it works fine for numbers on the hour, not for
minutes though. I fixed that.)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim h As Single, m As Single
If ((Target.Column = 2) Or (Target.Column = 3)) And ((Target.Row >=
5) And (Target.Row <= 35)) Then
Debug.Print IsNumeric(Target)
If IsNumeric(Target) Then
Application.EnableEvents = False
h = (Target \ 100) / 24
m = (Target Mod 100) / 1440
Target = h + m
Target.NumberFormat = "h:mm"
If ((Target = "00:00:00") Or (Target = "00:00") Or (Target
= "0")) Then
Target.NumberFormat = "General"
Target = ""
End If
Application.EnableEvents = True
End If
End If
End Sub

Cheers,
Jason Lepack

 

[Ofer]

Thanks it works perfectly now

You were changing the target to "" after you made enableevents true so
it was calling itself all over again and strangely enough, "" is
numeric. Why it didn't loop infinitely, I don't know, but it loops
quite a bit.

I changed your code so that it doesn't blink, but it doesn't do what
you said it would. It didn't work right for me the way I downloaded
it. (On further review it works fine for numbers on the hour, not for
minutes though. I fixed that.)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim h As Single, m As Single
If ((Target.Column = 2) Or (Target.Column = 3)) And ((Target.Row >=
5) And (Target.Row <= 35)) Then
Debug.Print IsNumeric(Target)
If IsNumeric(Target) Then
Application.EnableEvents = False
h = (Target \ 100) / 24
m = (Target Mod 100) / 1440
Target = h + m
Target.NumberFormat = "h:mm"
If ((Target = "00:00:00") Or (Target = "00:00") Or (Target
= "0")) Then
Target.NumberFormat = "General"
Target = ""
End If
Application.EnableEvents = True
End If
End If
End Sub

Cheers,
Jason Lepack