| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| blink a cell | Excel Discussion (Misc queries) | |||
| How do i set up blink a perticular cell? | Excel Discussion (Misc queries) | |||
| Is it possible to cause an Excell calculated value to blink? | Excel Discussion (Misc queries) | |||
| how to have a conditional box to blink or sound | Excel Worksheet Functions | |||
| Is It Possible To Make A Cell Blink.. | Excel Programming | |||
Linear Mode
