finding the blink cause

  • Thread starter Thread starter Ofer
  • Start date Start date
O

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
 
Have you tried inserting
Application.ScreenUpdating = False
at the beginning of the macro and
Application.ScreenUpdating = True
just before the End Sub line?
 
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
 
Thanks it works perfectly now

Jason said:
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
Click to expand...
 
Back
Top