|
finding the blink cause
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 |
finding the blink cause
Have you tried inserting
Application.ScreenUpdating = False at the beginning of the macro and Application.ScreenUpdating = True just before the End Sub line? "Ofer" wrote in message ups.com... 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 |
finding the blink cause
Tried it,
... not working it's like the whole worksheet is blinking KC Rippstein wrote: Have you tried inserting Application.ScreenUpdating = False at the beginning of the macro and Application.ScreenUpdating = True just before the End Sub line? "Ofer" wrote in message ups.com... 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 |
finding the blink cause
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 wrote: 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 |
finding the blink cause
Thanks it works perfectly now
Jason Lepack wrote: 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 wrote: 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 |
| All times are GMT +1. The time now is 04:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com